Mysql – SQL: Join on multiple tables

join;MySQL

I am trying to join the following tables:

Table 1: users (id, name)
Table 2: pivot_table (user_id, user_id2)

This table hold the relationship between the users. One user can be related to many other users.

Table 3: contacts (id, user_id, name)

I am trying to select a user and then find its related users and contacts.
I have tried to use the join, i am successfully able to get either the contacts or the users but not the both.

I used the following query:

select
    contacts.id as contact_id,
    users.id as user_id,
    pivot.user_id2 as user2_id
from
    `contacts`
    inner join `users` on  `contacts`.`user_id` = `users`.`id`
    inner join `pivot` as `pivot` on `pivot`.`user_id` = `contacts`.`user_id`

Suppose i have the following data in the tables
Users:

id, name
1, Sahil
2, Shubham
3, Xyz

Pivot table data

id, user_id, user_id2
1, 1, 2

Contacts table data

id, user_id, name
1, 1, Abc
2, 1, XYZ

Now what is get from the query is

contact_id, user_id, user_id2
1, 1, 2
2, 1, 2

but what i need is

contact_id, user_id, user_id2
1, 1, null
2, 1, null
null, 1, 2

I am not able to figure out what wrong i am doing.

Thanks

Best Answer

You need to look into the different JOIN types.

With an INNER JOIN as you have here the result will only include rows for cases where a match is found in all the sources.

With a LEFT OUTER JOIN or RIGHT OUTER JOIN the result will include rows for all data in one side of the join even if there is no match at the other side (columns SELECTed from the other side will come out as NULL).

With a FULL OUTER JOIN you will get rows output for all three conditions (match both sides, data on left with no match on right, and data on right with no match on left). I think this is what you want for the join between contacts and users.

Note that the word "outer" is usually optional so you could write LEFT JOIN instead of LEFT OUTER JOIN

This page has a useful diagram of join types: http://stevestedman.com/2015/03/mysql-join-types-poster/

It suggests that mySQL doesn't support FULL OUTER joins (which I didn't know was the case) so you will need to UNION two selects, one with a RIGHT join and one with a LEFT to get the sort of result you are looking for.

Related Question