0

I have the following three tables:

t1:
id | name
---------
1  | joe
2  | jon

t2:
id | street 
--------------
1  | "8th St."    

t3:
id | postcode
--------------
1  | "OC24 8BB"

How, using MySQL SELECT, can I specify a value for the common unique attribute 'id', and receive a single row combining the values from the three tables, with NULL values if none were found?

For example, if id = 1, I would expect the result:

id | name | street    | postcode
----------------------------------
1  | joe  | "8th St." | "OC24 8BB"

Whereas if id = 2, the following would be returned:

id | name | street | postcode
------------------------------
2  | jon  | NULL   | NULL

My current best attempt at the select is the following:

     SELECT A.id, A.name, B.street FROM t1 as A LEFT JOIN t2 AS B
     WHERE A.id = 1
     UNION ALL
     SELECT A.id, B.postcode FROM t1 as A LEFT JOIN t2 as B
     WHERE A.id = 1;

However this does not return the null values I require, I feel there is a simple solution however I have been looking for a while now and not found anything that has worked for me. Thank you if you can help shine some light on this problem.

1

You do not need UNION in this case, simple LEFT JOIN

SELECT 
   a.id, 
   b.street, 
   c.postcode
FROM t1 a
LEFT JOIN t2 b ON a.id=b.id
LEFT JOIN t3 c ON a.id=c.id

should work

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.