SQL statement with several joins

Question

I must be honest and tell you that I am not good at database queries and this question is probably quite simple.

I have three tables

Post
    ID
    entry
Category
    ID
    name
CategoryBinding
    ID
    postID
    categoryID

My normal query is to get all posts with the categories it is put into

SELECT * FROM `Post` AS `p` 
LEFT JOIN `CategoryBinding` AS `cb` ON p.ID = cb.postID 
LEFT JOIN `Category` AS `c` ON cb.categoryID = c.ID

The returned query for this is something like:

ID    entry    ID    name    ID    postID    categoryID
1     entry1    1     php      1      1            1
1     entry1    2     asp      1      1            2

2     entry2    1     php      1      2            1

3     entry3    null  null    null    null        null

Now I want to get all posts that belongs to a certain category ID with all the categories the post is put into.
I.E I want to get the same things as in the first query BUT only the posts that belong to a certain category. Now I only want to get the posts that belongs the category asp. That is

ID    entry    ID    name    ID    postID    categoryID
1     entry1    1     php      1      1            1
1     entry1    2     asp      1      1            2

Do you know how I can do this?

I will be very thankful if someone helps me since this is more like a "Do the work for me" question.

Answer 1

SELECT * 
FROM `Post` AS `p` 
LEFT JOIN `CategoryBinding` AS `cb` ON p.ID = cb.postID 
LEFT JOIN `Category` AS `c` ON cb.categoryID = c.ID
INNER JOIN `Post` AS `p2` ON p.id = p2.id
WHERE p.id in
(
    SELECT p2.id
    FROM `Post` as `p2`
    LEFT JOIN `CategoryBinding` AS `cb` ON p2.ID = cb.postID 
    LEFT JOIN `Category` AS `c` ON cb.categoryID = c.ID
    WHERE c.id = @SomeCategory
)

Alright, final shot.

This will return duplicate rows, just add a group by to whatever you want.