Problem with MySQL query

Question

I have three tables (user, friends, posts) and two users (user1 and user2).

When user1 adds user2 as friend then user1 can see the posts of user2 just like on Facebook. But only the posts after the date when user1 added user2 as friend. My query is like this:

SELECT * FROM posts p JOIN friends f ON p.userid = f.friendid 
AND s.time >= f.friend_since WHERE f.myid='user1id'

Then it gives me all the posts about user2 after the date since added as friend. But problem is that it hides user1’s posts and shows only user2’s posts after added date.

user1 should see all of its own posts and all the posts of user2, that user2 has posted after the date it was added as friend.

How can I fix it?

Answer 1

You can join the results from another query using UNION, i.e.

SELECT * FROM posts p JOIN friends f ON p.userid = f.friendid AND s.time >= f.friend_since WHERE f.myid='user1id' UNION SELECT * from posts p WHERE p.userid='user1id'

Answer 2

Get all friends ids of user1 and then select all posts .. WHERE ( p.userid IN ( FRIENDS_IDS ) AND s.time >= f.friend_since ) OR p.userid = USER_ID This way is much more faster than unions and joins in one query.