Issue with SQL query (working with 3 tables)

Question

Currently I'm working on the Social Network. I decided to make friends relationship (like FB does) instead of following users (like TW does) and here come my problem.

So, I have 3 tables (Users, Posts, Friends).

Friends Table

Post Table

Users Table

I would like to do it so that if the user has no friends (or just sent an invitation [friends_type == 1 in friends table]), only his posts are displayed.

My idea for this is: SELECT * FROM users, posts, friends WHERE friends_status=4, but when user has friends I would like to see posts from him and his friends.

And of course this is my SQL query I made: SELECT * FROM posts, users, friends WHERE user_id=post_author AND friends_status=4 AND (friend_friendid=user_id OR friend_userid=user_id) ORDER BY post_id DESC.

Basically, I want to achieve what Facebook has on the home page (never-ending timeline where you can scroll and scroll). My question is: How to build that query?

Answer 1

This is one method, using a subrequest to find friends :

select *
from posts
where post_author = {user_id}
   or post_author in
   (
     select friend_friendid from friends
     where friend_userid = {user_id}
       and friend_status != 1
   )