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" <[EMAIL PROTECTED]>
To: "PostgreSQL List" <[EMAIL PROTECTED]>
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 [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
