I have 4 tables which make together all information about article which can be in multiple category and has many tags
1) article - id, name, text
2) article_category - id, name
3) article_tag - id, name
4) article_taxonomy - primaryID, secondaryID, type
- primaryID is id of article and secondaryID is id of category or tag - this is specified by type ("category", "tag")
And query to select it:
Code: Select all
SELECT article.id, GROUP_CONCAT(article_tag.name) AS tag, GROUP_CONCAT(article_category.name) AS category
FROM article
LEFT JOIN article_taxonomy csk ON article.id = csk.primaryID AND csk.type = "category"
LEFT JOIN article_category ON csk.secondaryID = article_category.id
LEFT JOIN article_taxonomy cst ON article.id = cst.primaryID AND cst.type = "tag"
LEFT JOIN article_tag ON cst.secondaryID = article_tag.id
WHERE article.id = 1 GROUP BY article.ide.g. I have 3 tags for article - myself, trip, fun, but only one category - private
The result of my query is tag => myself, trip, fun and category => private, private, private
I don't know why there is private three times. Any advise how to avoid it?