getting mysql data for different users

Question

If i have two users one users have many posts in post table i.e

postid, post, userid, time(i.e timestamp)

and I have another table for friends i.e

id, friendid, userid, friend_sinc(i.e timestamp)

Now I want that users friend should see the posts of user1 after the date on which he added the user1 as friend. Not the posts older than the date on he added him as friend. How can I done it in single MySQL query?

Answer 1

This would look up all posts from people after they became friends with user 12:

select     *
from       Posts p
join       Friends f
on         p.userid = f.friendid
and        p.time >= f.friend_since
where      f.userid = 12

Answer 2

SELECT p.*
FROM post p, friend f
WHERE p.userid = f.userid AND p.time >= f.friend_sinc;

Edit: I guess it could be interpreted either way as which persond id is which, but the point is clear.

Answer 3

With a union

select p.* from posts p where p.userid = <my_user_id> 
union
select p.* from posts p, friends f where 
p.userid = f.friendid and 
f.userid = <my_user_id> and 
p.time >= f.friend_since

Or you consider yourself to be your own friend, that is, there is an entry in friend with friendid = <my_user_id> and userid = <my_user_id> and friend_since = <date user was created>, and then the second query works and return your own post as well. I don't think you get duplicates.