Re: UNION with more restrictive DISTINCT

Oops. That statement will "prefer" the t1.name instead of t2.name. It should
be COALESCE(t2.name,t1.name)

Another option is:

SELECT t2.id
, t2.name
FROM t2
UNION
SELECT t1.id
, t1.name
FROM t2
WHERE NOT EXISTS
(SELECT NULL
FROM t1 JOIN t2 ON t1.id = t2.id
)

----- Original Message -----
From: "Vincent Hikida" <vhikida(at)inreach(dot)com>
To: "peter pilsl" <pilsl(at)goldfisch(dot)at>; "PostgreSQL List"
<pgsql-general(at)postgresql(dot)org>
Sent: Saturday, December 18, 2004 12:40 AM
Subject: Re: [GENERAL] UNION with more restrictive DISTINCT

> One solution is
>
> SELECT COALESCE(t1.id,t2.id)
> , COALESCE(t1.name,t2.name)
> FROM t1 FULL JOIN t2 ON t1.id = t2.id
>
>
> ----- Original Message -----
> From: "peter pilsl" <pilsl(at)goldfisch(dot)at>
> To: "PostgreSQL List" <pgsql-general(at)postgresql(dot)org>
> Sent: Wednesday, December 15, 2004 1:03 PM
> Subject: [GENERAL] UNION with more restrictive DISTINCT
>
>
>>
>> I'd like to UNION two queries but the distinct-criteria for UNION should
>> not be all columns in the queries, but only one.
>>
>> example. two tables:
>>
>> test=# select id,name from t1;
>> id | name
>> ----+------
>> 1 | bob
>> 2 | mike
>> (2 rows)
>>
>> test=# select id,name from t2;
>> id | name
>> ----+---------
>> 1 | bob
>> 2 | mike j.
>> (2 rows)
>>
>>
>> # select id,name from t1 union select id,name from t2;
>> id | name
>> ----+---------
>> 1 | bob
>> 2 | mike
>> 2 | mike j.
>> (3 rows)
>>
>>
>> now I want a construct that returns me only one row for each id. If there
>> are different names for that id's in the different tables, the name of t2
>> should be chosen.
>>
>> like:
>>
>> # select id,name from t1 UNION ON (id) AND SUPERMAGICPREFER (t2.name)
>> select id,name from t2;
>> id | name
>> ----+---------
>> 1 | bob
>> 2 | mike j.
>> (2 rows)
>>
>>
>> What is an appropriate approach to this? If I use my UNION-query as
>> subquery for a SELECT DISTINCT ID, I loose the name, which is important.
>>
>> thnx.
>> peter
>>
>>
>>
>> --
>> mag. peter pilsl
>> goldfisch.at
>> IT-management
>> tel +43 699 1 3574035
>> fax +43 699 4 3574035
>> pilsl(at)goldfisch(dot)at
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>