CODEWITHSUNDEEP
mysqlsqljoin
sunny93
sunny93

Reputation: 131

SQL inner join not showing expected results

I want to fetch all the posts made by me and my friends only. But this query shows the posts even to a user who has no friends.

SELECT f.user_id,f.friend_id, p.user_id AS Post_BY, p.post_text, p.post_date  
FROM users u 
INNER JOIN friends f USING (user_id) 
INNER JOIN post p USING(user_id)
WHERE f.user_id = 6 
OR f.friend_id = 6
OR p.user_id = f.user_id

Upvotes: 0

Views: 72

Answers (2)

xQbert
xQbert

Reputation: 35323

This should return: All friends posts and your own. it assumes friends table friend_Id is a foreign key to users.user_ID. which ties to p.user_ID. The friend.friend_Id is not explicitly defined though can be inferred from OR f.friend_id = 6 in the original post

We join to post using an or condition to return your and your friends posts. We limit to just you from the users table.

order of the tables doesn't matter, I re-arranged for my benefit in thinking this through.

SELECT f.user_id,f.friend_id, p.user_id AS Post_BY, p.post_text, p.post_date  
FROM users u 
INNER JOIN friends f 
  on u.user_id = f.user_id
INNER JOIN post p 
  on f.user_id = P.user_id
  or f.friend_id = p.user_id
WHERE (u.user_id = 6)

After comment saying it's not working...Lets break it down:

  • Join of users to friends on user_Id should give you... yourself and friends IDs; however in two separate columns
  • Joining to posts based on Friends OR user_ID means all your posts, as well as your friends.
  • Where clause limits to just a single users and friends, user being 6.

I can't see why this wouldn't work. So I would need a SQL fiddle example or sample data with expected results at this time; or I don't understand what you're after.

In fact: here's an SQL fiddle showing it returns users and friends posts. In this example, user 6 has two friends 1,2. notice 6,1,2 posts are returned but 3's is not.

To handle that... we use a case statement and null out the friend ID on posts made by the original user. Though a simple union of two sets may be more efficient and maintainable.

SELECT distinct f.user_id, 
case when u.user_ID = 6 then null else f.friend_id end as Friend_ID, p.user_id as `Post_BY`, p.post_text, p.post_date  
FROM users u 
INNER JOIN friends f 
  on u.user_Id = f.user_Id
INNER JOIN posts p 
  on f.user_ID = P.user_Id
  or f.friend_Id = p.user_ID
WHERE (u.user_id = 6)

Upvotes: 1

Paul
Paul

Reputation: 9022

First of all, you want posts, so make it your primary table. Then, you could create a subselect to find all your friends' IDs.

SELECT p.user_id AS Post_BY, p.post_text, p.post_date 
FROM post p
WHERE p.user_id = 6
    OR p.user_id IN (SELECT friend_id FROM friends WHERE user_id = 6)

This is the simplest case and assumes that for a friend relationship there are two entries in the friends table (1 > 2 AND 2 > 1).

If there is only one entry in the friends table, you need to add another query to the subquery:

SELECT p.user_id AS Post_BY, p.post_text, p.post_date 
FROM post p
WHERE p.user_id = 6
    OR p.user_id IN (
        SELECT friend_id FROM friends WHERE user_id = 6
        UNION
        SELECT user_id FROM friends WHERE friend_id = 6)

Upvotes: 1

Related Questions