CODEWITHSUNDEEP
mysql
ivan savvidis
ivan savvidis

Reputation: 21

Mysql query returning each result multiple times

I want to show all the posts from the people I am following including my own posts too. While it is working as intended, I have a problem: I get the same posts multiple times.

Here is my query:

SELECT posts.id, posts.body, posts.posted_at, posts.postimg, posts.likes, users.`username` 
FROM users, posts, followers
WHERE (posts.user_id = followers.user_id OR posts.user_id = :userid)
       AND users.id = posts.user_id
       AND follower_id = :userid
ORDER BY posts.posted_at DESC;

Can anyone help me? Thank you in advance.

Upvotes: 0

Views: 88

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31832

The reason is in this condition:

WHERE (posts.user_id = followers.user_id
        OR posts.user_id = :userid)

For every user you are following, you will get all your own posts, thus creating duplicates of own posts.

You should use a UNION (ALL) query. To avoid the full query duplication, you can first select all user IDs in a subquery:

    SELECT followers.user_id
    FROM followers
    WHERE followers.follower_id = :userid
    UNION ALL SELECT :user_id

Then join it with your tables:

SELECT posts.id,
       posts.body,
       posts.posted_at,
       posts.postimg,
       posts.likes,
       users.`username`
FROM (
    SELECT followers.user_id
    FROM followers
    WHERE followers.follower_id = :userid
    UNION ALL SELECT :user_id
) uids
JOIN users ON users.id = uids.user_id
JOIN posts ON posts.user_id = uids.user_id

Upvotes: 1

Sendoa
Sendoa

Reputation: 1

You should try to use:

Select DISTINCT(fields)

From....

Where ...

The DISTINCT will show only one row for every equal results.

I think that your query is working like a cross join.

I hope this helps you, and sorry for my english.

Upvotes: 0

Related Questions