这个SQL查询出了什么问题？

I have two tables: photographs, and photograph_tags. Photograph_tags contains a column called photograph_id (id in photographs). You can have many tags for one photograph. I have a photograph row related to three tags: boy, stream, and water. However, running the following query returns 0 rows

我有两张桌子:照片和photograph_tags。 Photograph_tags包含名为photograph_id的列(照片中的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

Is something wrong with this query?

这个查询有问题吗?

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

3 个解决方案

#1

to get all photos with the 3 tags (OR MORE) you specified. Start with the Tags and join the photos.

使用您指定的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

testing the above code:

测试上面的代码:

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

#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

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

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

will give you photographs with at least three tags.

会给你带有至少三个标签的照片。

#1