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