• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

tsql show record for zero count value

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
javierpdx
Asked:
javierpdx
1 Solution
 
chaauCommented:
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
 
javierpdxAuthor Commented:
Thanks!  I appreciate the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now