使用和条件[重复]从数据透视表中选择

This question already has an answer here:

这个问题在这里已有答案：

Select values that meet different conditions on different rows? 6 answers
选择满足不同行的不同条件的值？ 6个答案

I'm struggling for hours with something that seems so easy but I cant make it to work or find something similar in Google.

我正在努力工作几个小时似乎很容易，但我不能让它工作或在谷歌找到类似的东西。

I have 2 tables

我有2张桌子

images

and

和

tags

They have a relation of Many to Many so I have another pivot table named image_tag

它们具有多对多的关系，因此我有另一个名为image_tag的数据透视表

What Im trying to achive is select all images that has tag_id=4 and tag_id=1

我试图获得的是选择所有具有tag_id = 4和tag_id = 1的图像

My first attemp was something like this:

我的第一次尝试是这样的：

SELECT * from images as a INNER JOIN
image_tag as b on a.id=b.image_id 
WHERE b.tag_id=4 and b.tag_id=1

Of couse this gave me 0 result as you cant use AND condition directly to pivot.

当我不能直接使用AND条件来转动时，这给了我0结果。

After that I tried this:

之后我尝试了这个：

SELECT * FROM images as a
INNER JOIN image_tag as b on a.id=b.image_id
WHERE b.tag_id IN (1,4)

This returns all the images that either has tag_id=1 or tag_id=4 tried also Inner joining the same pivot table but cant make it to work no matter what

这将返回所有具有tag_id = 1或tag_id = 4的图像也尝试内部加入相同的数据透视表但无法使其工作无论什么

EDIT: Adding the sql fiddle. http://sqlfiddle.com/#!9/1726b0/1 the result should be images with ids 4,5,6,7

编辑：添加sql小提琴。 http://sqlfiddle.com/#!9/1726b0/1结果应该是带有ids 4,5,6,7的图像

2 个解决方案

#1

Use group by and having to get all image_id's meeting the criteria and use the resulting id's for join.

使用group by并且必须使所有image_id符合条件，并使用生成的id进行连接。

SELECT a.* 
FROM images as a
INNER JOIN (select image_id 
            from image_tag 
            where tag_id IN (1,4) 
            group by image_id
            having count(distinct tag_id)=2
           ) b on a.id=b.image_id

#2

You can do this with just the table image_tag. You have to join it with itself in order to get all the combinations. This way you can then select the rows witch will have both tags.

您只需使用table image_tag即可完成此操作。您必须自己加入才能获得所有组合。这样，您就可以选择具有两个标记的行。

SELECT a.image_id 
FROM image_tag as a 
inner join image_tag as b on a.image_id=b.image_id 
where a.tag_id=4 and b.tag_id=1

#1