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.