Clever Geek Handbook

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

I first search the table tags, resulting in all rows being returned along with their additional data from the table links (on which I am making the join). Table tags work in such a way that there can be multiple lines for auto_id (1 tag per line, so multiple tags are multiple lines).

What I want to do is that instead of just getting a few lines back that match the auto _id and query. I want every tag to return for every auto_id found on 1 line (in some form, but that means all tags are comma separated in 1 field), so the group_concat (which obviously doesn't work at the moment - I 'you added its 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 made my case clear.

Many thanks,

Ice

0


source to share


1 answer


If I understand what you are 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
+1


source






More articles:


All Articles