Need some help with a MySQL subquery count
- by Ferdy
I'm running into my own limits of MySQL query skills, so I hope some SQL guru can help out on this one. The situation is as follow:
I have images that can be tagged. As you might expect this is stored in three tables:
Image
Tag
Tag_map (maps images to tags)
I have a SQL query that calculates the related tags based on a tag id. The query basically checks what other tags were used for images for images using that tag. Example:
Image1 tagged as "Bear"
Image2 tagged as "Bear" and "Canada"
If I throw "Bear" (or its tag id) at the query, it will return "Canada". This works fine. Here's the query:
SELECT tag.name, tag.id, COUNT(tag_map.id) as cnt
FROM tag_map,tag
WHERE tag_map.tag_id = tag.id AND tag.id != '185' AND tag_map.image_id IN
(SELECT tag_map.image_id FROM tag_map INNER JOIN tag ON tag_map.tag_id = tag.id WHERE tag.id = '185')
GROUP BY tag_map.id LIMIT 0,100
The part I'm stuck with is the count. For each related tag returned, I want to know how many images are in that tag. Currently it always returns 1, even if there are for example 3. I've tried counting different columns all resulting in the same output, so I guess there is a flaw in my thinking.
