What happened to this SQL query?

Question

I have two tables: photos and photos. Photograph_tags contains a column called photograph_id (id in photos). You can have many tags for one photo. I have a photo associated with three tags: boy, creek and water. However, executing the following query returns 0 rows

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 there something wrong with this request?

 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 

Answer 1

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

 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 

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 

Answer 3

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

will provide you photos with at least three tags.