Solved

tsql show record for zero count value

Posted on 2014-01-13
2
271 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

'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 …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 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

14 Experts available now in Live!

Get 1:1 Help Now