Mysql Tag Query - Repeated values

发布于 2022-08-23 04:59:23 字数 998 浏览 0 评论 1

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 query:

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

紫轩蝶泪 2022-08-24 03:25:51 1 楼
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
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击“接受”或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。