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@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Akbar
>Sent: donderdag 28 december 2006 13:10
>To: pgsql-general@postgresql.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
>
