Mysql Tag Query - Repeated values
I'm working on a tag-based search. The user can search one tag or multiple tags. I've got 3 tables: Content, Content_Tags and Tags. Content_Tags does the link between content and tags. The query has to return all the information of the content retrieved, this includes a concatenated string of all tags from that content.
Using an example from other question I've managed to reach this q开发者_JAVA技巧uery:
SELECT content.Name,GROUP_CONCAT(t2.Tag SEPARATOR ' ') FROM content
JOIN content_tags ct1 ON content.ContentID = ct1.ContentID
JOIN tags t1 on ct1.TagID = t1.TagID AND t1.Tag IN('grass','texture')
JOIN content_tags ct2 ON ct2.ContentID = content.ContentID JOIN tags t2 ON ct2.TagID = t2.TagID GROUP BY content.ContentID;
The query works fine when searching for 1 tag, but using IN('grass','texture')
the GROUP_CONCAT will return 'grass grass texture texture' (repeating the tags).
I don't know MySQL this deep, how could I fix this problem?
SELECT content.name,
(
SELECT GROUP_CONCAT(cta.tag)
FROM content_tags cta
WHERE cta.contentID = ct.contentID
)
FROM content_tags ct
JOIN content c
ON c.contentId = ct.contentId
WHERE ct.tag IN ('grass', 'texture')
GROUP BY
ct.contentId
HAVING COUNT(*) = 2
精彩评论