Need some help with a MySQL subquery count

Question

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.

Answer 1

Some food for thought

• I noticed you use id in your tag & image table and tablename_id in your tag_map table. Each his own offcourse, but I found it to be much easier if an id is named the same everywhere. I would rename the id's in tag & image to tag_id & image_id respectively.
• It seems your id's are character strings. I've taken the liberty to use integers in the examples.

The following example uses SQL Server. It should not be to hard to adjust the SQL Statement to MySQL.

Test data

DECLARE @tag TABLE (id INTEGER, tag VARCHAR(32))
DECLARE @image TABLE (id INTEGER, image VARCHAR(32))
DECLARE @tag_map TABLE (image_id INTEGER, tag_id INTEGER)

INSERT INTO @tag
SELECT 185, 'Bear' 
UNION ALL SELECT 186, 'Canada'

INSERT INTO @image
SELECT 1, 'image1'
UNION ALL SELECT 2, 'image2'

INSERT INTO @tag_map
SELECT 1, 185
UNION ALL SELECT 2, 185
UNION ALL SELECT 2, 186

SQL Statement

SELECT  t.tag
        , t.id
        , cnt = (SELECT COUNT(*) FROM @tag_map WHERE tag_id = t.id)
FROM    @tag_map m
        INNER JOIN @tag t ON t.id = m.tag_id
        INNER JOIN (
          SELECT  m.image_id
          FROM    @tag_map m
          WHERE   m.tag_id = 185
        ) i ON i.image_id = m.image_id
WHERE   t.id <> 185

Answer 2

Your code not working right because you select just images "associated with a choosen tag", but not images, "associated with tags associated with image associated with choosen tag" (I hope, I used correct recursion depth :) ).

You can do this with subselects:

SELECT tag.id, tag.name, COUNT(DISTINCT tag_map.image_id) as cnt
  FROM tag_map, tag
 WHERE tag_map.tag_id = tag.id
   AND tag.id != 185
   AND tag_map.tag_id IN (
     SELECT sub1.tag_id FROM tag_map AS sub1 WHERE sub1.image_id IN (
       SELECT sub2.image_id FROM tag_map AS sub2 WHERE sub2.tag_id = 185
     )
   )
GROUP BY tag.id, tag.name;