Select Count with Left join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Count with Left join

Author

Topic

Vaio
Starting Member

2 Posts

Posted - 2010-04-02 : 06:57:35

Hi everybody,

I'm struggling to acheive complicated SELECT statement. Could you please try to help me?

I've 3 tables.

Table 1: Categories
CatID
CatDescription

Table 2: Topics
TopicID
TopicDescription
CatID

Table 3: SelectedTopics
STID
TopicID
Status (boolean)

What I'd like to do is count the total selected topics which status is (True) per Category.

The desired output need to be as follows:
-----------------------
Category | Total SelectedTopics
-----------------------
Category 1 | 2
Category 2 | 3
Category 3 | 0
Category 4 | 1
-------------------------

I try to solve it but I end up with the following results:
-----------------------
Category | Total SelectedTopics
-----------------------
Category 1 | 2
Category 2 | 3
Category 4 | 1
-------------------------

Look at: Category 3 | 0 <<<< IS NOT DISPLAYED

Can you please help me to do it.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-04-02 : 07:36:06

try like this


SELECT c.catid,c.catdescription,s.cnt
FROM categories c
LEFT JOIN (SELECT t.catid,COUNT(DISTINCT t.topicid) FROM topics t INNER JOIN selectedtopics AS s ON s.topicid = t.topicid
	AND s.status = 'TRUE' GROUP BY t.catid) AS s ON s.catid = c.catid