MySQL A Search, A Select of multiple rows, Joining and all in one query

Question

I am first doing a search on the table tags which results in all the rows returned together with their additional data from the table links (on which i do a join). The table tags works such that for an auto _id there can be multiple rows (1 tag per row so multiple tags is multiple rows).

What I want to do is that instead of just getting multiple rows back which match the auto _id and query. I want to get each tag back for each auto_id that is found, in 1 row (some form of distinct but that puts all the tags comma seperated in 1 field), hence the group_concat (which obviously is not working at the moment - i've added it as pseudo).

SELECT ta.auto_id, li.address, li.title, GROUP_CONCAT(SELECT tag FROM tags WHERE auto_id = ta.auto_id)
FROM `tags` AS ta
JOIN
links AS li
ON ta.auto_id = li.auto_id
GROUP BY ta.tag
WHERE ta.user_id = 1
AND (ta.tag LIKE '%query%')

I hope I've made my case clear.

Thank you very much,

Ice

Answer 1

If I understand what you're asking, something like this should do the trick:

SELECT ta.auto_id, li.address, li.title, GROUP_CONCAT(ta.tag) -- Get a comma separated list of all the tags
FROM tags AS ta
INNER JOIN links AS li ON ta.auto_id = li.auto_id
WHERE ta.user_id = 1
AND ta.tag LIKE '%query%'
GROUP BY li.id -- Group by links id, so you get one row for each link, which contians a list of tags