Gossamer Forum
Home : General : Perl Programming :

SQL joins...

Quote Reply
SQL joins...
I have a script (a small forum) I'm writing and I'm trying to minimize the number queries I need to perform. So, for my main page, I've managed to do one query to get the record for forum category and grap the number of posts, and while looping through the results, perform a second query to get the number of threads. Unformatately there seems to be a bug in my query or in mySQL...

Code:
select forums.*, count(*) as PostCount
from forums
left JOIN posts ON forums.ID=posts.ForumID
group by forums.ID

I have set up 3 categories, the first two have two posts each, while the third has none. I expected the count() to return 2, 2, and 0... but on the category with no posts, a count of 1 is still returned. Why is that?

--Philip
Links 2.0 moderator
Quote Reply
Re: [King Junko II] SQL joins... In reply to
oooh.. it pays to search on google groups :-)

instead of
Code:
count(*)

do
Code:
sum(posts.ID is not null)

--Philip
Links 2.0 moderator
Quote Reply
Re: [King Junko II] SQL joins... In reply to
cool... even better... everything at once Sly
Code:
select
forums.*,
sum(posts.ID is not null) as PostCount,
count(distinct posts.ThreadID) as ThreadCount
from forums
left join posts ON forums.ID=posts.ForumID
group by forums.ID
order by forums.Title

--Philip
Links 2.0 moderator