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
