Need help with MySQL subquery calculation

Question

I come across my MySQL query skill limits, so I hope some SQL guru can help with this. The situation is as follows:

I have images that can be tagged. As you might expect, this will be stored in three tables:

• Picture
• Tag
• Tag_map (matches images with tags)

I have an SQL query that computes related tags based on a tag id. The query basically checks which other tags were used for images for images using this tag. Example:

Image1 is labeled “Bear”
Image2 is labeled “Bear” and “Canada”

If I drop “Bear” (or its tag identifier) ​​into the request, it will return “Canada”. It works great. Here's the request:

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 for is the bill. For each linked tag returned, I want to know how many images are in this tag. Currently, it always returns 1, even if there is, for example, 3. I tried to count different columns, all leading to the same conclusion, so I think there is a flaw in my thinking.

Answer 1

, , , , , , . (, :)).

:

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;

Answer 2

• , id tablename_id tag_map. , , , . tag_id image_id .
• , . .

SQL Server. SQL- MySQL.

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

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