0

I have 3 DB tables

POSTS
+----------+----------+----------+
| id       |  title   |  content |
+----------+----------+----------+

CATEGORIES
+----------+----------+
| id       |  title   |
+----------+----------+

POSTS_CATEGORIES
+----------+----------+--------------+
| id       |  post_id |  category_id |
+----------+----------+--------------+

Now lets say I have the following posts:

id=1, title=birds and it belongs to category 1 and 4

id=2, title=apple and it does not belong to any categories

My Query:

SELECT p.id, p.title, p.content
GROUP_CONCAT(DISTINCT p_c.category_id ORDER BY p_c.category_id ASC SEPARATOR ',') AS categories
FROM posts_categories AS p_c, posts as p
WHERE p.id = 1 AND p.id = p_c.post_id
GROUP BY p_c.post_id

This query works for id=1 but when a post does not have any categories, like id=2 it won't find anything. I've tried to modify this a bit, but I always end up with no results or all the fields as null when I'm looking for id=2

I know I could get around this with 2 queries, but was trying to do it with one query. I'm using mysql 5.5.27

0

This should get all posts and a list of categories (If no categories, then 'None')

SELECT
    p.id, p.title, p.content,
    IFNULL(GROUP_CONCAT(p_c.category_id
    ORDER BY p_c.category_id ASC SEPARATOR ','),'None') category
FROM
    posts p
    LEFT JOIN posts_categories p_c ON p.id = p_c.post_id
    LEFT JOIN categories c ON p_c.category_id = c.id
WHERE
    p.id = 1
GROUP BY
    p.id
;    

Give it a Try !!!

  • I'm not looking for all posts, just 1 post with all categories associated with it, regardless if the categories are empty or not. – hardbizkit Jun 18 '14 at 22:09
  • Added WHERE p.id = 1 to the query... – RolandoMySQLDBA Jun 18 '14 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.