CODEWITHSUNDEEP
mysqlsql
user3500000
user3500000

Reputation: 33

Join 3 tables only if they have information on 1 table

I have 3 table

|           Likes           |            |    Friends     |             |     Item        |
-----------------------------            ------------------             -------------------
| UserID  | LikeID | itemID |            | user1  |  user2|             |     itemID      |

I would like to join the 3 tables based on if the user has liked an item in the Likes table and the user must also be friends with the user in the Friends table and both of them have liked the item.

The way the Friends table is set up is if a user adds a friend the userID will be placed in either user1 or user2 depending who requested the friend first, so the userID of say 1 can be in either user1 column or user2 column and users friend will be in the opposing column.

Then I would do an inner join on itemID table to get the item both of the users liked.

I am no SQL expert so I am finding this very difficult and any code examples I give you would be rubbish. I have tried all types of left joins inner joins right joins I've joined the crap out of these tables but I do not get the correct data.

Any steer in the right direction on how to accomplish this would be great I am out of ideas. Please let me know of any further information I can give you.

Upvotes: 0

Views: 31

Answers (1)

WSC
WSC

Reputation: 992

I believe this does what you are after:

select a.user1, b.likeid, a.user2, c.likeid, d.itemid
from friends a
inner join likes b on a.user1 = b.userid
inner join likes c on a.user2 = c.userid
inner join item d on (b.itemid = d.itemid and c.itemid = d.itemid);

Here's an sqlfiddle demonstrating it: http://sqlfiddle.com/#!4/ff9d59/11

Upvotes: 1

Related Questions