please help me in this 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:

$sql = mysql_query("SELECT * FROM posts p JOIN friends f ON 
        p.currentuserid = f.friendid AND p.time >= f.friend_since OR 
        p.currentuserid='user1id' WHERE f.myid='user1id' 
        ORDER BY p.postid DESC LIMIT 20");

it is working all the way fine but with a little problem.....!! it displays user2, user3 (all the users as friends of user1) posts for single time but shows user1 posts multiple.......i.e

user2. hi
user1. userssfsfsfsfsdf
user1. userssfsfsfsfsdf
user3. dddddddd
user1. sdfsdsdfsdsfsf
user1. sdfsdsdfsdsfsf

but i in database it is single entry/post why it is happening........!!

How can I fix it?

Answer 1

I'm not a SQL expert, but I think your problem is in the JOIN condition. I cannot see a way how you can join with posts and friends and get the result that you need. A SQL expert may know this, but for me it's just too difficult.

If I were you I would break the problem down in 2 parts:

1. Select the user's own posts

2. Select the user's friend's posts

For example, you can do this by using 2 different conditions and do the join with the friends table in a sub query (I have not tested this!):

select * 

from posts p

where 

p.currentuserid = 'user1id'

or 

p.postid in 

(
select p2.postid
from posts p2 
join friend f on p2.currentuserid = f.friendid 
where p2.time >= f.friend_since and f.myid='user1id' 
)

Another way to do it is to use a union (also not tested..):

select * 

from posts p

where 

p.currentuserid = 'user1id'

union 

select p2.*

from posts p2 

join friend f on p2.currentuserid = f.friendid 

where p2.time >= f.friend_since and f.myid='user1id'

Answer 2

I think, the easiest solution is to use GROUP BY statement on column posts.userId to remove duplicate entries. However it is not optimized way to solve the problem.

Answer 3

The reason you're getting the posts of all of user1's friends is you're not qualifying which friend's posts the query should return.

Add a f.friendid = 'user2id' (or whatever the column name is) in there before the WHERE clause.