Hussam Abedlatif Hussam Abedlatif - 1 year ago 59
MySQL Question

SQL queries getting records from table

SELECT posts.id, posts.body, posts.posted_at, posts.likes, posts.dislikes, users.`userName`
FROM users,posts,interests
WHERE (posts.user_id =3
OR interests.follower_id =3 AND posts.topics IN(SELECT interest_id FROM interests WHERE follower_id = 3))
AND users.ID = posts.user_id
AND posts.topics = interests.interest_id
ORDER BY posts.posted_at DESC


This query works fine when user_id =3 is the only person following a certain topic in the interest table. The interest table include id, follower_id, and (interest_id which are topics such as sports).

The posts table include a column for topics which are topics users can follow and once you follow a topic i insert it in the interests table. Now I am trying to retrieve posts made by users, the posts I want to get can either be posts made by me (there is a column in posts called user_id which is the id of the user who posted it) or posts of topics I am following.

The problem here is when i run this query i get duplicate posts because some other user is following the same topic.

Answer Source

If you aren't querying any fields from interests, it probably shouldn't be in the join. Converting its usage to an in or exists condition will eliminate your duplicates. Additionally, you should note that implicit joins (i.e., listing several tables in the from clause) are considered to be a bad practice, and you should probably use an explicit join instead:

SELECT   posts.id, posts.body, posts.posted_at, posts.likes, posts.dislikes,
         users.userName
FROM     posts 
JOIN     users ON users.id = posts.user_id 
WHERE    posts.user_id = 3 OR 
         posts.topics IN (SELECT interest_id 
                          FROM   interests 
                          WHERE follower_id = 3)
ORDER BY posts.posted_at DESC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download