Selecting with 2 JOINs to get a count and a concatenated string

3 tables:

items(item_id, ...)

tags(item_id, tag_name)

downloads(item_id, ...)

How do I select a single item together with the number of occurrences of this item in the downloads table and all tag_name values associated with that item?

Tried:

SELECT 
    ...,
    COUNT(downloads.item_id) AS download_count,
    GROUP_CONCAT(tags.tag_name SEPARATOR ":") AS tags
FROM items 
LEFT JOIN tags ON items.item_开发者_如何学Cid = tags.item_id 
LEFT JOIN downloads ON items.item_id  = downloads.item_id 
WHERE items.item_id = 123

Doesn't work. Returns the tags multiple times. As many times as the occurrences of the item in the "downloads" table are.

I'm not surprised you're getting multiples, because you have created a Cartesian product between tags and downloads.

Try this:

SELECT 
    ...,
    COUNT(DISTINCT downloads.download_id) AS download_count,
    GROUP_CONCAT(DISTINCT tags.tag_name SEPARATOR ":") AS tags
FROM items 
LEFT JOIN tags ON items.item_id = tags.item_id 
LEFT JOIN downloads ON items.item_id  = downloads.item_id 
WHERE items.item_id = 123

However, I'd recommend doing this in two queries. You don't have to do everything in a single query, and sometimes it's faster to run two simpler queries:

SELECT 
    ...,
    COUNT(*) AS download_count,
FROM items 
LEFT JOIN downloads ON items.item_id  = downloads.item_id 
WHERE items.item_id = 123

SELECT 
    GROUP_CONCAT(tags.tag_name SEPARATOR ":") AS tags
FROM items 
LEFT JOIN tags ON items.item_id = tags.item_id 
WHERE items.item_id = 123