Re: 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
>