left join - MYSQL UNION DISTINCT

Question

I have two selects that I'm currently running as a UNION successfully.

(SELECT a.user_id,
          a.updatecontents AS city,
          b.country
   FROM userprofiletemp AS a
   LEFT JOIN userattributes AS b ON a.user_id=b.user_id
   WHERE typeofupdate='city')
UNION DISTINCT
  (SELECT a.user_id,
          c.city,
          c.country
   FROM userverify AS a
   LEFT JOIN userlogin AS b ON a.user_id=b.user_id
   LEFT JOIN userattributes AS c ON a.user_id=c.user_id
   WHERE b.active=1
     AND a.verifycity=0);

The results come back like this:

100 Melbourne Australia
200 NewYork America
300 Tokyo Japan
100 Sydney Australia

The catch is the query will bring duplicate user_id (in this case the 100). The details in the first query take precedent for me and if the user_id is repeated in the second query I don't need it.

Is there a way to get a UNION to be DISTINCT on a column? in this case the user_id? Is there a way to do the above call and not get duplicate user_id's - drop the second. Should I re-write the query differently and not use a UNION. Really want it as one query - I can use to SELECT's and PHP to weed out duplicate if necessary.

thx Adam

Answer 1

No. You cannot specify which exact field you need to distinct with. It only works with the whole row.

As of your problem - just make your query a subquery and in outer one GROUP BY user_id

SELECT * FROM 
(SELECT a.user_id,a.updatecontents as city,b.country
FROM userprofiletemp AS a
LEFT JOIN userattributes AS b ON a.user_id=b.user_id
WHERE typeofupdate='city')

UNION DISTINCT

(SELECT a.user_id,c.city,c.country
FROM userverify AS a
LEFT JOIN userlogin AS b ON a.user_id=b.user_id
LEFT JOIN userattributes AS c ON a.user_id=c.user_id
WHERE b.active=1 AND a.verifycity=0) x
GROUP BY user_id