Solved

tsql show record for zero count value

Posted on 2014-01-13
2
272 Views
Last Modified: 2014-01-13
I've been trying to change my join to show all Seminars, even if it has a zero count but have not been able to get this so far.  It currently only shows records in which Selected Seminars have at least one record.
SELECT        SS.SSeminarSelected SSS, COUNT(SS.SSeminarID) AS 'Seminar Count', S.RoomNumber SRN, S.Capacity Cap, S.SeminarDetails SSD, S.CumCreditsAbove30 SCCA, S.SIsActive SIA
FROM            StudentSeminars AS SS RIGHT OUTER JOIN
                         Seminars AS S ON SS.SSeminarSelected = S.SeminarID
WHERE        (S.SeminarTerm = 43) AND (SS.SSubmitTerm = 43)
GROUP BY S.RoomNumber, SS.SSeminarSelected, S.SeminarDetails, S.Capacity, S.SSort, S.CumCreditsAbove30, S.SIsActive
ORDER BY S.SSort

Open in new window

I appreciate any assistance.
0
Comment
Question by:javierpdx
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39778028
Provided that seminars and studentseminars link by seminarterm column, you can rewrite your query like this:
SELECT        SS.SSeminarSelected SSS, 
COUNT(SS.SSeminarID) AS 'Seminar Count', 
S.RoomNumber SRN, 
S.Capacity Cap, 
S.SeminarDetails SSD, 
S.CumCreditsAbove30 SCCA, 
S.SIsActive SIA
FROM            Seminars AS S LEFT OUTER JOIN
                       StudentSeminars AS SS   ON SS.SSeminarSelected = S.SeminarID 
                       AND S.SeminarTerm = SS.SSubmitTerm -- this line may not even be required, comment it if it does not work
WHERE        S.SeminarTerm = 43
GROUP BY S.RoomNumber, SS.SSeminarSelected, S.SeminarDetails, S.Capacity, S.SSort, S.CumCreditsAbove30, S.SIsActive
ORDER BY S.SSort

Open in new window

0
 

Author Closing Comment

by:javierpdx
ID: 39778043
Thanks!  I appreciate the help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Subquery Syntax Assistance 13 50
Access 2003 form, automatically input query parameter from list 5 44
how to fix this error 14 57
SQL join help to a thrid table 51 76
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now