MySQL - SELECT from multiple tables by unique attribute, return one row with all values

Question

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.

Answer 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