1

This has got to be a common thing, but I'm not having luck Googling around.

I have a table of categories:

Table A
catid | text
1     | Category A
2     | Category B
3     | Category C

This table is then joined to a map that keeps track of what categories a photo is in

Table B
catid | photoid
1     | 1
2     | 1
1     | 2
3     | 3

What I need to do is filter the query so that I return only the photos that are in EVERY category selected. For example:

  1. If the user selects categories A and B, I return photo 1.
  2. If the user selects just category A, I return photos 1 and 2.
  3. If the user selects categories A,B and C - I return nothing.

Thanks for any help you can give.

-Matt

user3116226
  • 107
  • 1
  • 9

3 Answers3

1

One of the easiest ways to do it is:

SELECT 
    * 
FROM 
    B 
WHERE 
    B.catid IN (1, 2) 
GROUP BY 
    B.photoid 
HAVING 
    COUNT(B.photoid) = 2

To match 3 categories you would do:

SELECT 
    * 
FROM 
    B 
WHERE 
    B.catid IN (1, 2, 3) 
GROUP BY 
    B.photoid 
HAVING 
    COUNT(B.photoid) = 3

You can also join the same table multiple times. Or do a sub-query. I would test a couple of different methods to see which executes most quickly for your dataset.

Dave
  • 3,658
  • 1
  • 16
  • 9
  • This is the route I went. I was already building out most of this query in PHP. Just had to add a bit for the HAVING clause. Thanks much for the help! – user3116226 Feb 03 '14 at 16:25
0

Use Not Exists subquery

Select distinct photoid
From photoCategory pc
Where Not Exists 
    (Select * From category c
     Where catId <> pc.catId)
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
-1

Answer is here...

https://stackoverflow.com/a/17026879/520857

sorry for misleading answer just now

And btw, what you're trying to do is called an INTERSECT, which MySQL doesn't inherently support thus the weird solution in the link.

Community
  • 1
  • 1
Populus
  • 7,470
  • 3
  • 38
  • 54