Mysql query returning each result multiple times

Question

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.

Please create an example with sample data and expected result. — Paul Spiegel
– Paul Spiegel, Commented Mar 25, 2018 at 14:32

Paul Spiegel · Accepted Answer · 2018-03-25 16:10:42Z

1

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

edited Mar 25, 2018 at 16:10

answered Mar 25, 2018 at 14:43

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sendoa · Accepted Answer · 2018-03-25 13:40:19Z

0

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.

answered Mar 25, 2018 at 13:40

Sendoa

1

Collectives™ on Stack Overflow

Mysql query returning each result multiple times

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related