Prevent duplicate values in LEFT JOIN

by

I like to call this problem “cross join by proxy”. Since there is no information (WHERE or JOIN condition) how the tables department and contact are supposed to match up, they are cross-joined via the proxy table person – giving you the Cartesian product. Very similar to this one:

  • Two SQL LEFT JOINS produce incorrect result

More explanation there.

Solution for your query:

SELECT p.id, p.person_name, d.department_name, c.phone_number
FROM   person p
LEFT   JOIN (
   SELECT person_id, min(department_name) AS department_name
   FROM   department
   GROUP  BY person_id
   ) d ON d.person_id = p.id
LEFT   JOIN (
   SELECT person_id, min(phone_number) AS phone_number
   FROM   contact
   GROUP  BY person_id
   ) c ON c.person_id = p.id;

You did not define which department or phone number to pick, so I arbitrarily chose the minimum. You can have it any other way …

Leave a Comment

tech