SQL JOIN met 3 tabellen

Kort en bondig: ik wil 3 tabellen LEFT JOINen, zou moeten gaan met deze query (voor zover ik weet): [code:1:116e37bd5d]SELECT post.*, user.user_name, COUNT(comment.comment_id) AS comment_count FROM post LEFT JOIN (user, comment) ON (post.post_author = user.user_id AND post.post_id = comment.comment_parent_post_id) ORDER BY post.post_date DESC LIMIT 0, 10[/code:1:116e37bd5d] Maar deze SQL geeft mij maar 1 rij, terwijl er zeker een 4tal rijen in post zitten. Ziet iemand de fout? Alvast bedankt!

Probeer dit eens:

[code:1:d8b5bb5062]
SELECT post.*, user.user_name, COUNT(comment.comment_id) AS comment_count
FROM post
LEFT JOIN user ON post.post_author = user.user_id
LEFT JOIN comment post.post_id = comment.comment_parent_post_id
ORDER BY post.post_date DESC
LIMIT 0, 10
[/code:1:d8b5bb5062]

Als [i:4395465d05]ik[/i:4395465d05] moet raden wat de bedoeling is, wordt het dit...

[code:1:4395465d05]
SELECT post., user.user_name, (SELECT COUNT() FROM comment WHERE post.post_id = comment_parent_post_id) AS comment_count
FROM post
INNER JOIN user ON post.post_author = user.user_id
ORDER BY post.post_date DESC
LIMIT 0, 10
[/code:1:4395465d05]

@ iJoost: die query werkt wel, bedankt! Kan je wel even uitleggen hoe ie percies werkt, want ik snap em niet helemaal :D.