CODEWITHSUNDEEP
mysqlleft-joinwhere-clause
Gonçalo Baltazar
Gonçalo Baltazar

Reputation: 303

MySQL JOIN with WHERE clause, not returning all results

I'm working on PHP/MySQL based 'Blog Post' System, the posts can have multiple Categories, I have 2 tables:

posts:

PostId   Content
0        POST0
1        POST1
2        POST2
3        POST3

post_categories:

PostId   CategoryId
0        1
0        2
0        3
1        2
1        4
2        3
3        1

(I omitted some columns)
I also have a 3rd table 'categories', that describes the categories, but that's not relevant.

Giving a CategoryId (eg: 2), I want to return all posts containing that CategoryId, on the following form:

PostId  Contents  CategoriesIds
0       POST0     1, 2, 3
1       POST1     2, 4

(Both post 0 and post 1 must be returned, they both have the CategoryId 2)

The problem is that using this query:

SELECT p.PostId, p.Content, GROUP_CONCAT(pc.CategoryId SEPARATOR ',') AS CategoriesIds
FROM posts AS p
LEFT JOIN post_categories AS pc ON p.PostId=pc.PostId
WHERE pc.CategoryId = 2 GROUP BY p.PostId

both posts are returned but NOT all Category Ids,

PostId  Content   CategoriesIds
0       POST0     2
1       POST1     2

I want to return ALL posts with CategoryId 2 but still returning ALL CategoriesIds for those posts.

Is it possible to do so?
Thanks

Upvotes: 1

Views: 170

Answers (2)

John Woo
John Woo

Reputation: 263883

The simpliest solution without using IN clause would be

SELECT a.postID, c.content, 
       GROUP_CONCAT(b.categoryID ORDER BY b.categoryID ) CategoryList
FROM
    (
      SELECT  PostID
      FROM    post_categories b
      WHERE   CategoryID = 2
    ) a
    INNER JOIN post_categories b
      ON a.postID = b.postID
    INNER JOIN post c
      ON c.PostID = b.PostID
GROUP BY a.postID, c.content

SQLFiddle Demo

Upvotes: 0

Jamie
Jamie

Reputation: 816

Looks like you need to separate out the selection of the posts with categoryId = 2 from the selection of the fields - try something like this:

SELECT p.PostId, p.Content,
GROUP_CONCAT(pc.CategoryId SEPARATOR ',') AS CategoriesIds

FROM posts AS p
LEFT JOIN post_categories AS pc ON p.PostId=pc.PostId

where p.PostId in 
  ( select PostId from post_categories where CategoryId = 2)

GROUP BY p.PostId

Upvotes: 1

Related Questions