MySQL - multiple joins

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
benny17
Forum Newbie
Posts: 1
Joined: Mon Oct 05, 2009 1:25 pm

MySQL - multiple joins

Post by benny17 »

Howdy,
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.id
But my query makes almost things good but I have problem when the count of categories isn't similar to count of tags ..
e.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?
Post Reply