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?

hichris123 · Accepted Answer · 2014-01-25 18:54:36Z

1

You can join the results from another query using UNION, like this:

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'

edited Jan 25, 2014 at 18:54

hichris123

10.3k15 gold badges58 silver badges70 bronze badges

answered Dec 6, 2009 at 16:55

Connorhd

2,47614 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

testkhan Over a year ago

i have done that but it is giving me the mysql_fetch_array error

hsz · Accepted Answer · 2009-12-06 16:58:31Z

0

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.

answered Dec 6, 2009 at 16:58

hsz

153k63 gold badges268 silver badges320 bronze badges

1 Comment

hsz Over a year ago

Ok, missed time part up there. But this query below should works fine.

SELECT * FROM posts AS p LEFT JOIN friends f ON ( p.userid = f.friendid AND p.time >= f.friend_since ) OR ( f.myid = 'user1id' )

Collectives™ on Stack Overflow

Problem with MySQL query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related