Posted by rbaba on 07/24/05 14:21

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

[Back to original message]