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.