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@inreach.com>
To: "peter pilsl" <pilsl@goldfisch.at>; "PostgreSQL List"
<pgsql-general@postgresql.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@goldfisch.at>
> To: "PostgreSQL List" <pgsql-general@postgresql.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@goldfisch.at
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>>      subscribe-nomail command to majordomo@postgresql.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@postgresql.org so that your
>      message can get through to the mailing list cleanly
>