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
>
