按多个值过滤一对多关系

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:

我需要做的是过滤查询,以便我只返回选中的每个类别的照片。例如:

If the user selects categories A and B, I return photo 1.

如果用户选择类别A和B,则返回照片1。

If the user selects just category A, I return photos 1 and 2.

如果用户仅选择类别A,则返回照片1和2。

If the user selects categories A,B and C - I return nothing.

如果用户选择类别A,B和C - 我什么都不返回。

Thanks for any help you can give.

谢谢你提供的所有帮助。

-Matt

3 个解决方案

#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:

要匹配3个类别,您需要:

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.

您也可以多次加入同一个表。或者做一个子查询。我会测试几种不同的方法来查看哪种方法对数据集的执行速度最快。

#2

Use Not Exists subquery

使用不存在子查询

Select distinct photoid
From photoCategory pc
Where Not Exists 
    (Select * From category c
     Where catId <> pc.catId)

#3

-1

Answer is here...

答案就在这里......

https://*.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.

顺便说一下,你正在尝试做的是一个INTERSECT,MySQL本身并不支持链接中的奇怪解决方案。

#1