Chris Chris - 4 months ago 34
SQL Question

MySQL JOIN 3 tables search by WHERE on ID from 1

I seem to be struggling grasping the concept of joining 3 tables on one ID.

USER Table

|id |
|:----:|
|1 |
|2 |


POST table

|id |userid | content|
|:----:|:----:|:----:|
|1 |1 |HELLO |
|2 |1 |THERE |
|3 |2 |WORLD |


friend table

|id | userid | friendid |
|:----:|:------:|:--------:|
| 1 | 1 | 2 |


I want to provide the ID from the USER table, and get ALL the CONTENT from POSTS where the POST.USERID is the FRIEND.USERID and FRIEND.FRIENDID
So USER.ID = '1' would bring back all the POST.CONTENT(in this case).

Really hope you can help, been annoying me for hours.

Answer

Following your description, you would not need to join to the user table, you could directly use the friends table, as you have the userid there. But this should give you all posts from userid 5 and his that persons friends.

SELECT post.* 
FROM post 
INNER JOIN friends ON post.userid = friends.userid OR post.userid = friends.friendid 
WHERE friends.userid =1

To not query duplicates use this:

SELECT post.*, users.firstname
FROM post 
INNER JOIN users ON users.userid = post.userid
WHERE post.userid = 1

UNION

SELECT post.*, users.firstname 
FROM friends
INNER JOIN post ON friends.friendid = post.userid 
INNER JOIN users ON users.userid = friends.friendid
WHERE friends.userid = 1