Query to join specific tables

0

I need to join 3 different tables. But the result I want is: When the reference exists it should join, when it does not exist it should show blank or NULL.

My structure:

Contact table

+------+------------+
|  ID  |    Nome    |
+------+------------+
|  1   | Contato #1 |
+------+------------+
|  2   | Contato #2 |
+------+------------+
|  3   | Contato #3 |
+------+------------+
|  4   | Contato #4 |
+------+------------+
|  5   | Contato #5 |
+------+------------+

Contact_Email Table

+------+------------+-----------------------------+
|  ID  | id_contato |           email             |
+------+------------+-----------------------------+
|  1   |      1     | [email protected] |
+------+------------+-----------------------------+
|  2   |      2     | [email protected] |
+------+------------+-----------------------------+
|  3   |      2     | [email protected] |
+------+------------+-----------------------------+

I made this inner join:

select * 
from contato
inner join contato_email on (contato.id_contato = contato_email.id_contato)

But with this it only returns me the users who have an email.
Note that a user may have more than one e-mail associated with it. I want to fetch all contacts and if I have email I must merge the data.

+------+------------+-----------------------------+
|  ID  |    Nome    |          email              |
+------+------------+-----------------------------+
|  1   | Contato #1 | [email protected] |
+------+------------+-----------------------------+
|  2   | Contato #2 | [email protected] |
+------+------------+-----------------------------+
|  2   | Contato #2 | [email protected] |
+------+------------+-----------------------------+
|  3   | Contato #3 |                             |
+------+------------+-----------------------------+
|  4   | Contato #4 |                             |
+------+------------+-----------------------------+
|  5   | Contato #5 |                             |
+------+------------+-----------------------------+

This is the desired return.
Thank you.

    
asked by anonymous 25.07.2016 / 20:12

1 answer

5

Try to replace Inner with LEFT JOIN

select * 
from contato
left join contato_email on (contato.id_contato = contato_email.id_contato)
    
25.07.2016 / 20:14