Posted by Stefan Rybacki on 07/24/05 14:54

rbaba wrote:
> Hi all,
>
> I have:
>
> Table: articles
> --------------
> articleid
> categoryid
> userid
> title
> body
>
> Table categories
> ---------------
> categoryid
> name
> description
>
> Table users
> ------------
> userid
> firstname
> email
>
> Table comments
> -----------------
> commentid
> articleid
> title
> body
>
> I Have this quey:
>
> SELECT a.articleid, a.categoryid, a.userid, a.title, c.name, u.firstname,
> count(co.commentid) as nb
> FROM articles a, categories c, users u, comments co
> WHERE c.categoryid = a.categoryid AND u.userid = a.userid AND co.articleid =
> a.articleid GROUP BY a.articleid
>
> Result:
> articleid | categoryid | userid | title | name | firstname | nb |
> 1 1 1 title1 categorie1 paul 3
> 2 1 1 title2 category1 paul 4
> 3 1 2 title3 category1 fred 1
> 4 2 3 title4 category2 laura 2
>
> As you see the quey return only articles that have comments.
> I want all articles. The query must returns also articles with 0 comments.
>
> the result should be:
>
> articleid | categoryid | userid | title | name | firstname | nb |
> 1 1 1 title1 categorie1 paul 3
> 2 1 1 title2 category1 paul 4
> 3 1 2 title3 category1 fred 1
> 4 2 3 title4 category2 laura 2
> 5 2 2 title5 category2 fred 0
> 6 2 3 title6 category2 laura 0
>
> thx
>
>
>

Use an OUTER JOIN

SELECT a.articleid, a.categoryid, a.userid, a.title, c.name, u.firstname,
count(co.commentid) as nb
FROM articles a LEFT OUTER JOIN categories c ON a.articleid=co.articleid, users u,
comments co
WHERE (c.categoryid = a.categoryid AND u.userid = a.userid) AND (co.articleid =
a.articleid OR co.articleid IS NULL) GROUP BY a.articleid


(untested)

Regards
Stefan

[Back to original message]