Select rows that match all specified rows using a join table

Question

I am trying write a query which would return all programs that have category a and category b. The table structure is as follows:

Program

Program_Category

program_id | category_id
1000       | 1
1000       | 2

Category

id          | name
1           | category a
2           | category b

As you can see program 1000 has category a and category b. The query i am trying to use to retrieve the program is

SELECT DISTINCT t1.id 
FROM PROGRAM t1 
LEFT OUTER JOIN (PROGRAM_CATEGORY t2 
                 JOIN CATEGORY t0 ON (t0.id = t2.category_id)
) ON (t2.program_id = t1.id) 
WHERE ((t0.name = 'category a') 
   AND (t0.name = 'category b'))

This is currently returning 0 rows.

Joachim Isaksson · Accepted Answer · 2014-03-13 20:24:43Z

1

A regular JOIN can do it in a fairly straight forward way;

SELECT pc1.program_id 
FROM program_category pc1
JOIN program_category pc2
  ON pc1.program_id = pc2.program_id
JOIN category c1
  ON c1.category_id = pc1.category_id AND c1.name = 'category a'
JOIN category c2
  ON c2.category_id = pc2.category_id AND c2.name = 'category b'

An SQLfiddle to test with.

answered Mar 13, 2014 at 20:24

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

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

1 Comment

lucasweb Over a year ago

Thanks this worked. Looks like I was missing having a join per category that I wanted to search on.

Olvathar · Accepted Answer · 2014-03-13 20:23:14Z

1

I couldn't guess what your query is trying to do but I think this will work for you

SELECT count(*), p.ID FROM Program p
INNER JOIN Program_Category pc ON pc.program_id = p.id
INNER JOIN Category c ON pc.category_id = c.category_id 
WHERE c.category_name = 'category a' or c.category_name = 'category b'
GROUP BY p.ID HAVING count(*) >= 2

This will get any program wich appears twice matching any of these categories.

This will NOT work if (program_id, category_id) is NOT a UNIQUE KEY. I mean, this will fail if Program_Category can have twice the pair 1000,1

answered Mar 13, 2014 at 20:23

Olvathar

5513 silver badges10 bronze badges

Comments

marco.jantke · Accepted Answer · 2014-03-13 20:25:16Z

0

You are accessing t0.id which is actually t0.category_id according to your table definition.

answered Mar 13, 2014 at 20:25

marco.jantke

914 bronze badges

1 Comment

lucasweb Over a year ago

That was a typo, it should be id, not category id, I have fixed by tabled definition. Thanks

trogdor · Accepted Answer · 2014-03-13 20:31:11Z

0

Try joining with two instances of category. This query checks that p is related with a ' category a' and a 'category b', and will work even if a (program_id, category_id) is not unique:

SELECT distinct p.id
FROM PROGRAM p JOIN program_category pc1 ON p.id = pc1.program_id
       JOIN category c1 ON pc1.category_id = c1.id
       JOIN program_category pc2 ON p.id = pc2.program_id
       JOIN category c2 ON pc2.category_id = c2.id
WHERE c1.category_name = 'category a'
AND c2.category_name = 'category b';

edited Mar 13, 2014 at 20:31

answered Mar 13, 2014 at 20:25

trogdor

1,65614 silver badges17 bronze badges

Collectives™ on Stack Overflow

Select rows that match all specified rows using a join table

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related