CODEWITHSUNDEEP
mysqlsqljoin
NLemay
NLemay

Reputation: 2383

Return intersection of 3 tables with joins

I have 3 tables : news, users_regions_favorites and users_categories_favorites. Each news has a region and a category.

I want to return all news that are related the a user's favorites (by the regions or categories he likes). And never return a news twice, so no UNION.

enter image description here

Is there a way to do this with join?

Upvotes: 1

Views: 1250

Answers (2)

xlecoustillier
xlecoustillier

Reputation: 16351

You can indeed use UNION:

SELECT n.id 
FROM   news n 
       INNER JOIN users_categories_favorites c 
               ON c.id = n.catid
WHERE c.uid = 1 -- your user here
UNION 
SELECT n.id 
FROM   news n 
       INNER JOIN users_regions_favorites r 
               ON r.id = n.regid
WHERE r.uid = 1; -- your user here

UNION will deduplicate, only UNION ALL won't.

But you can also do this using only JOINs:

SELECT DISTINCT n.id 
FROM   news n 
       LEFT JOIN users_categories_favorites c 
              ON c.id = n.catid 
       LEFT JOIN users_regions_favorites r 
              ON r.id = n.regid 
WHERE c.uid = 1 OR r.uid = 1; -- your user here

Upvotes: 1

Steve Marion
Steve Marion

Reputation: 289

you could make (schematically) :

select distinct 
news where exists users_regions_favorites
union
news where exists users_categories_favorites

Upvotes: 0

Related Questions