Re: select union with table name

From: "Joris Dobbelsteen" <Joris(at)familiedobbelsteen(dot)nl>
To: "Akbar" <akbarhome(at)gmail(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: select union with table name
Date: 2006-12-28 12:41:44
Message-ID: 73427AD314CC364C8DF0FFF9C4D693FF549C@nehemiah.joris2k.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Try:
select blue.name, 'blue' from blue union select red.name, 'red' from
red;

Not tested, but that should work.
One thing to remember:
If blabla is in both blue and red, it will appear twice, instead of only
once as in your example.

- Joris

>-----Original Message-----
>From: pgsql-general-owner(at)postgresql(dot)org
>[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Akbar
>Sent: donderdag 28 december 2006 13:10
>To: pgsql-general(at)postgresql(dot)org
>Subject: [GENERAL] select union with table name
>
>Hi, I have two tables.
>create table blue (
> id serial primary key,
> name text not null,
> kill text not null
>);
>
>create table red (
> id serial primary key,
> name text not null,
> kiss text not null
>);
>
>select blue.name from blue union select red.name from red;
>give me this:
>name
>'blabla'
>'bubu'
>'haha'
>'kkk'
>
>I want this:
>name table_name
>'blabla' blue
>'bubu' blue
>'haha' red
>'kkk' red
>
>Could I?
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

Browse pgsql-general by date

  From Date Subject
Next Message Richard Broersma Jr 2006-12-28 13:00:52 Re: select union with table name
Previous Message Akbar 2006-12-28 12:09:33 select union with table name