Solved

tsql NULL record not showing with CASE

Posted on 2013-12-16
4
282 Views
Last Modified: 2013-12-16
My query is not showing my NULL values.  What am I missing?  Thanks!

SELECT        AccessList.ALMYID, AccessList.FirstName, AccessList.LastName, AccessList.ClassStanding, Seminars.SeminarDetails, AccessList.TermGPA, 
                         AccessList.MYCompletedCredits, StudentSeminars.SHasAttended, StudentSeminars.SSeminarID, AccessList.CumulativeGPA, AccessList.CompletedCredits, 
                         AccessList.IsTransfer, Demographics.DEmail, 
						 CASE WHEN (StudentSignature.SSigID IS NULL) 
						 THEN 'No'
						 ELSE 'Yes'
						 END AS Signed
FROM            AccessList INNER JOIN
                         StudentSeminars ON AccessList.ALMYID = StudentSeminars.SMYID INNER JOIN
                         Seminars ON StudentSeminars.SSeminarSelected = Seminars.SeminarID INNER JOIN
                         Demographics ON StudentSeminars.SMYID = Demographics.DMYID INNER JOIN
                         StudentSignature ON StudentSeminars.SMYID = StudentSignature.SSMYID
WHERE (Seminars.SeminarID = @SeminarID)

Open in new window

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
4 Comments
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 400 total points
ID: 39723023
What values are null? And how do you know?

Best guess is because all your joins are inner joins which means that any rows joined on null will mean the entire row doesn't show. You need to use left joins if you want to see null values.
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39723078
Put a relevant value for Null column, like
isnull(col1,'')

Open in new window

0
 
LVL 13

Assisted Solution

by:magarity
magarity earned 100 total points
ID: 39723212
If there is no corresponding record at all in the StudentSignature table then you need to use LEFT OUTER JOIN for it instead of INNER JOIN.  This is what I suspect since your CASE statement is phrased correctly for NULL in that field.
0
 

Author Closing Comment

by:javierpdx
ID: 39723253
Thanks. With LEFT JOIN, the No value did show as "No".
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 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