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

Question

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_id = 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.

Answer 1

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