Count query problem :(

Hi all,

Hope someone can help me with this ...

This is my query. The problem with it is that it only returns Areas with >0
Topics. So if a new Area is created, it hasn't a topic until one is
created, and it doesn't list!

Is it possible to return even those rows where the count is 0, or what is
another way around this?

Thanks in advance, Jen

SELECT dbo.chat_board.boardID, dbo.chat_area.areaID,
dbo.chat_area.name, dbo.chat_area.datecreated,
COUNT(dbo.chat_topic.topicID) AS counttopics
FROM dbo.chat_board INNER JOIN
dbo.chat_area ON
dbo.chat_board.boardID = dbo.chat_area.boardID INNER JOIN
dbo.chat_topic ON
dbo.chat_area.areaID = dbo.chat_topic.areaID
GROUP BY dbo.chat_board.boardID, dbo.chat_area.areaID,
dbo.chat_area.name, dbo.chat_area.datecreated

--
Fast Track On Line -Web Design and Development
Portfolio http://www.fasttrackonline.co.uk

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004On Wed, 2 Jun 2004 11:55:27 +0100, Jenny wrote:

>Hi all,
>Hope someone can help me with this ...
>This is my query. The problem with it is that it only returns Areas with >0
>Topics. So if a new Area is created, it hasn't a topic until one is
>created, and it doesn't list!
>Is it possible to return even those rows where the count is 0, or what is
>another way around this?
>Thanks in advance, Jen
>SELECT dbo.chat_board.boardID, dbo.chat_area.areaID,
> dbo.chat_area.name, dbo.chat_area.datecreated,
> COUNT(dbo.chat_topic.topicID) AS counttopics
>FROM dbo.chat_board INNER JOIN
> dbo.chat_area ON
> dbo.chat_board.boardID = dbo.chat_area.boardID INNER JOIN
> dbo.chat_topic ON
> dbo.chat_area.areaID = dbo.chat_topic.areaID
>GROUP BY dbo.chat_board.boardID, dbo.chat_area.areaID,
> dbo.chat_area.name, dbo.chat_area.datecreated

Hi Jenny,

Try replacing the second "INNER JOIN" with "LEFT OUTER JOIN". Check Books
Online for the details.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)