MySQL: 3 table join query?

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?

---

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' 

---

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.

---

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.

---

You really should give some idea of what the schema looks like so we don't have to make so many assumptions. I'm assuming the primary key of user is id, and friends has a userid as well as a friendid field. I'm also assuming posts.currentuserid is the id of the user who created the post. If not, replace it with posts.userid or whatever the correct field is.

The reason your query doesn't work right is that you need at least 2 joins. When creating a query, it's easiest to start with what you have and work up to what you want, one join at a time. Here's the query to get the posts that a particular user can read:

SELECT p.*
FROM user u
JOIN friends f ON u.id = f.userid
JOIN posts p ON ((u.id = p.currentuserid) OR (f.friendid = p.currentuserid AND p.time >= f.friend_since))
WHERE u.id = ?
ORDER BY p.postid DESC LIMIT 20

The second join is where the meat is. It specifies that in order to read a post it (a) has to be written by you or (b) has to be written by a friend of yours AFTER you friended them.

If you want to also get the name of the user who created the post (assuming user.name holds the user name) you need a 3rd join:

SELECT pu.name as 'Posted By', p.*
FROM user u
JOIN friends f ON u.id = f.userid
JOIN posts p ON ((u.id = p.currentuserid) OR (f.friendid = p.currentuserid AND p.time >= f.friend_since))
JOIN user pu ON p.currentuserid = pu.id
WHERE u.id = ?
ORDER BY p.postid DESC LIMIT 20