Solved

tsql show record for zero count value

Posted on 2014-01-13
2
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

718 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