mysql query joining tables to create newsfeed

Question

I want to retrieve rows from 3 tables - LikeTable, ClosetTable, FollowTable to display the activities of all the users a person is following. This is for a website. ClosetTable is the table for the user's products. LikeTable is the table for the likes of the products and Followtable for the following and followers.

Currently, I have

SELECT c.user, c.productName
FROM ClosetTable AS c
LEFT JOIN FollowTable AS f ON c.user = f.isFollowed
WHERE f.follows = 'tony'

This returns the rows of the person, 'tony' is following together with the productname. However, I want rows from the LikeTable and FollowTable in the same manner altogether. So can you suggest a way to do only 1 query to get rows from all 3 tables?

Answer 1

You can try something that look like this :

SELECT c.user, c.productName
FROM FollowTable AS f
INNER JOIN ClosetTable AS c ON c.user = f.isFollowed
INNER JOIN LikeTable AS l ON c.user = l.userliked
WHERE f.follows = 'tony'

I do some assumption for fields name since you didn't provide the structures of your tables.

Also, I suggested that you put the FollowTable in the FROM clause and then join it to ClosetTable since you put f.follows = 'tony' in your WHERE clause.

Otherwise, remove the WHERE clause and put the condition in the INNER JOIN. Something like :

LEFT JOIN FollowTable AS f ON c.user = f.isFollowed AND f.follows = 'tony'