I have 3 DB tables

| id       |  title   |  content |

| id       |  title   |

| 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

1 Answer 1


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

    p.id, p.title, p.content,
    ORDER BY p_c.category_id ASC SEPARATOR ','),'None') category
    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
    p.id = 1

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, 2014 at 22:09
  • Added WHERE p.id = 1 to the query... Jun 18, 2014 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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