Swany Swany - 5 months ago 8
SQL Question

SQL to return results if multiple matches in a pivot table

channel_data table
-------------
entry_id
content

channel_title table
-------------
entry_id
title

category_posts table
-------------
entry_id
category_id


sample data for category_posts

entry_id cat_id
2 10
2 11
2 30
2 40
3 10
3 11


I need to be able to query the data so I can say return data that have multiple categories. I have tried a few different ways but so far can't figure it out. I am sure it is simple so hoping someone can help out.

Here is where I got but can't figure out what I need in the where clause to make it work. If I only need one category it works but with 2 it does not.

SELECT distinct a.entry_id, b.title
FROM exp_channel_data as a
LEFT JOIN exp_channel_titles as b
ON a.entry_id = b.entry_id
LEFT JOIN exp_category_posts as c
ON a.entry_id = c.entry_id
WHERE c.cat_id = 10 and c.cat_id = 30

Answer

You can use a query like the following in order to get entry_id values being related to both cat_id values:

SELECT entry_id
FROM category_posts
WHERE cat_id IN (10, 30)
GROUP BY entry_id
HAVING COUNT(DISTINCT cat_id) = 2

Now you can use the above query as a derived table and JOIN to it in order to get the expected result:

SELECT distinct a.entry_id, b.title
FROM exp_channel_data as a 
LEFT JOIN exp_channel_titles as b ON a.entry_id = b.entry_id
LEFT JOIN (
   ... above query here
) AS c ON a.entry_id = c.entry_id