MYSQL IN statement return duplicates in select query

Question

I have a select query which is suppose to return news which is connected to several team ids. For this i'm using the IN statement with the selected team ids. For instance in this case i've chosen the teams with the id's 1 and 2 as you can see

The issue is that the teams might be connected to the same news and therefor i dont want duplicates of the same news? How can i make sure that there are not duplicate news even though the teams might be connected to the same news?

 SELECT news.id,
   news.title,
   news.url,
   news.image_url,
   news.date,
   news.news_text,
   website.url   AS website_url,
   website.image AS website_image
FROM   news,
       team,
       contain,
       website
WHERE  team.id IN ( 1, 2 )
       AND news.website_id = website.id
       AND team.id = contain.team_id
       AND contain.news_id = news.id
ORDER  BY news.date DESC
LIMIT  0, 30

Thats because its doing Cartesian product

Abhik Chakraborty
– Abhik Chakraborty

2015-04-28 10:19:57 +00:00
Commented Apr 28, 2015 at 10:19 — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 28, 2015 at 10:19

Abhik Chakraborty · Accepted Answer · 2015-04-28 10:25:44Z

1

Use explicit join with proper joining conditions

SELECT 
n.id, 
n.title, 
n.url, 
n.image_url, 
n.date, 
n.news_text, 
w.url as website_url, 
w.image as website_image 
from news n
join contain c on c.news_id = n.id
join team t on t.id = c.team_id
join website w on w.id = n.website_id
where  t.id in (1,2)
ORDER BY n.date DESC LIMIT 0, 30

Note that while doing join if the items i.e. 1,2 are preset multiple times on other tables then it will appear multiple times. If you want to get rid of this use group by

where  t.id in (1,2)
group by t.id

But this will choose random data from the joined tables for the associated ids 1,2.

answered Apr 28, 2015 at 10:25

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

Comments

javier_domenech · Accepted Answer · 2015-04-28 10:28:28Z

0

What about DISTINCT? In order to avoid duplicate results:

    SELECT DISTINCT news.id, news.title, news.url, news.image_url, 
news.date, news.news_text, website.url as website_url, 
website.image as website_image from news, team, contain, website 
WHERE team.id in (1,2)
 AND news.website_id = website.id
 AND team.id = contain.team_id
 AND contain.news_id = news.id 
 ORDER BY news.date DESC LIMIT 0, 30

answered Apr 28, 2015 at 10:28

javier_domenech

6,3036 gold badges41 silver badges60 bronze badges

Collectives™ on Stack Overflow

MYSQL IN statement return duplicates in select query

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