这个 SQL 查询有什么问题？

Question

我有两个表：photographs和photograph_tags。photograph_tags包含一个名为photograph_id（photographs中的id）的列。您可以为一张照片添加多个标签。我有一行照片与三个标签相关联：男孩、小溪和水。然而，运行以下查询返回0行。

SELECT p.*
FROM photographs p, photograph_tags c
WHERE c.photograph_id = p.id
AND (c.value IN ('dog', 'water', 'stream'))
GROUP BY p.id
HAVING COUNT( p.id )=3

这个查询有问题吗？

My tables look like so
-----------------------
photographs
-----------------------
id | title | location
------------------------
7  | asdf | c:\...


-----------------------
photograph_tags
-----------------------
id | photograph_id | value
1  | 7             | dog
2  | 7             | water
3  | 7             | stream
4  | 7             | mountains

I want to pull all photograph rows that relate to at least all three of the searched tags

- ThinkingInBits

Answer 1

要获取所有带有您指定的3个标签（或更多）的照片。从标签开始，将照片连接起来。

select
 p.id
from photographs p

left join photograph_tags c
on p.id = c.photograph_id
and c.value IN ('dog', 'water', 'stream')

group by p.id

having count(c.value) >= 3

测试上述代码：

create table #photograph_tags (
    photograph_id INT,
    value varchar(50)
)

create table #photographs (
    id int
)

insert into #photographs values (7)
insert into #photographs values (8)

insert into #photograph_tags values (7, 'dog')
insert into #photograph_tags values (7, 'water')
insert into #photograph_tags values (7, 'stream')
insert into #photograph_tags values (7, 'mountains')
insert into #photograph_tags values (8, 'stream')
insert into #photograph_tags values (8, 'mountains')

select * from #photographs
select * from #photograph_tags

select
    p.id
from #photographs p

left join #photograph_tags c
on p.id = c.photograph_id
and c.value IN ('dog', 'water', 'stream')

group by p.id

having count(c.value) >= 3


drop table #photograph_tags
drop table #photographs

- Daniel Brink

---

@ThinkingInBits：刚用你的数据测试了一下，完美地运行了。请检查一下是不是把你的id列混淆了。 - Daniel Brink

这看起来就像我的表格设置一样，但我没有得到任何结果。 - ThinkingInBits

@ThinkingInBits：很奇怪，我肯定得到了结果。也许你的数据库中没有带有这3个特定标签的照片。祝你好运，我得回去工作了。 - Daniel Brink

有两张图片包含了所有三个标签以及更多。 - ThinkingInBits

找到问题了...必须添加having count(c.value) >= 3...谢谢！我需要复习一下我的SQL :D - ThinkingInBits

Answer 2

SELECT p.* FROM photographs p join 
(select id, COUNT(id) as TagCount 
    FROM Photograph_Tags c
    WHERE c.value IN ('dog', 'water', 'stream')
    group by id) 
    as TagCounts on p.id = TagCounts.id
WHERE TagCount = 3

- DancesWithBamboo

Answer 3

SELECT p.* FROM photographs p WHERE (c.value IN ('dog', 'water', 'stream')) AND (SELECT COUNT(*) FROM photograph_tags c
WHERE c.photograph_id = p.id) >= 3;

这段代码将会返回至少有三个标签的照片。

- Turin