Solved

How to get a record with a COUNT to be returned from a JOIN, even when the COUNT is 0 (zero)?

Posted on 2007-03-26
5
190 Views
Last Modified: 2010-03-19
SQL Server 2005

How to get a record with a COUNT to be returned from a JOIN, even when the COUNT is 0 (zero)?

The following stored proc returns records when the COUNT is greater than zero. I know the SQL is doing exactly what I'm asking it to do - find records that meet the criteria through the JOIN. But, I'm brain farting on how to return the record even when the COUNT is zero. Do I need to use a subquery?==========================================================
CREATE PROCEDURE [dbo].[GetListOfSessionsWithCountByUserId]
@paramUserID int
AS
BEGIN
SELECT  dbo.tblSession.pk_lngSessionID,
              dbo.tblSession.fk_lngUserID,
              dbo.tblSession.dtmSessionDate,
              dbo.tblSession.strSessionClientName,  
              COUNT(dbo.tblSessionDetail.intActive) AS TotalActive

 FROM   dbo.tblSession
 INNER JOIN  dbo.tblSessionDetail
   ON dbo.tblSession.pk_lngSessionID = bo.tblSessionDetail.pk_lngSessionID
WHERE     (dbo.tblSession.fk_lngUserID = @paramUserID) AND (dbo.tblSessionDetail.intActive = 1)
GROUP BY dbo.tblSession.pk_lngSessionID, dbo.tblSession.fk_lngUserID, dbo.tblSession.dtmSessionDate, dbo.tblSession.strSessionClientName
END
==============================================
What's the right way to get ALL records, with a count that may be zero or greater?

Thanks!

Dennis

0
Comment
Question by:dtleahy
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18796115
Use a LEFT JOIN instead of an INNER JOIN
0
 

Author Comment

by:dtleahy
ID: 18796163
LEFT JOIN produces the same results
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 18796188
change to this:

SELECT  dbo.tblSession.pk_lngSessionID,
              dbo.tblSession.fk_lngUserID,
              dbo.tblSession.dtmSessionDate,
              dbo.tblSession.strSessionClientName,  
              COUNT(dbo.tblSessionDetail.intActive) AS TotalActive

 FROM   dbo.tblSession
 LEFT JOIN  dbo.tblSessionDetail
   ON dbo.tblSession.pk_lngSessionID = bo.tblSessionDetail.pk_lngSessionID AND dbo.tblSessionDetail.intActive = 1
WHERE  dbo.tblSession.fk_lngUserID = @paramUserID
GROUP BY dbo.tblSession.pk_lngSessionID, dbo.tblSession.fk_lngUserID, dbo.tblSession.dtmSessionDate, dbo.tblSession.strSessionClientName

I have simply moved the dbo.tblSessionDetail.intActive =1 condition to the join instead of the WHERE clause.
0
 

Author Comment

by:dtleahy
ID: 18796225
Cha ching! Excellent! Nightman, thank you very much for sharing your expertise!

Sometimes, I really feel like a SQL beginner. I would not have come up with that elegant solution - I'm sure I could have eventually done something using a nested subquery that first got all the rows for that user ID, but your solution is... elegant!

Dennis
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18796238
Dennis, it's an absolute pleasure. Good luck with your project!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Parsing the XML data to SQL Server 4 67
Where clause in stored procedure 8 60
Insert from Stored Procedure where some field/s > 0 7 45
Replace Dates in query 14 51
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question