Solved

Confusing Case Statement w/ Left Join

Posted on 2006-06-10
2
217 Views
Last Modified: 2012-08-14
Hey guys, I am trying to do a select w/ a left join like so:

SELECT  au.UserId,
            au.UserName,
            c.Title,
            ut.Grade,
            Case ua.status
                  when null then '0'
                  else ua.status
            End as WorkbookStatus,
            uv.Status AS VideoStatus,      
            c.ID AS ClassID,
            uc.CourseID
FROM         dbo.aspnet_Users AS au INNER JOIN
                      dbo.Users_Courses AS uc ON uc.UserID = au.UserId inner JOIN
                      dbo.Classes AS c ON c.CourseID = uc.CourseID left outer JOIN
                      dbo.Users_Tests AS ut ON ut.TestID = c.ID AND au.UserId = ut.UserID left outer JOIN
                      dbo.Users_Assignments AS ua ON ua.AssignmentID = c.ID AND au.UserId = ua.UserID left outer JOIN
                      dbo.Users_Videos AS uv ON uv.VideoID = c.ID AND au.UserId = uv.UserID
where au.username = 'nate'

The problem i am having is that the case statement above always defaults to the 'Else'.  For example the result of the above query returns the following data:

UserId                               Title                                              Grade       WorkbookStatus VideoStatus ClassID     CourseID
------------------------------------ -------------------------------------------------- ----------- -------------- ----------- ----------- -----------
6663227D-8208-44C9-93FC-BC5EE1DF14A4 Introduction to the Business Subscription          100         1              1           1000        1000
6663227D-8208-44C9-93FC-BC5EE1DF14A4 Starting Your Business                             58          1              1           1001        1000
6663227D-8208-44C9-93FC-BC5EE1DF14A4 System Certification                               0           0              0           1002        1001
6663227D-8208-44C9-93FC-BC5EE1DF14A4 Setting Up Your Business                           NULL        NULL           NULL        1003        1000

(4 row(s) affected)

But with the following modification:

SELECT  au.UserId,
            c.Title,
            ut.Grade,
            Case ua.status
                  when null then '0'
                  else '0'
            End as WorkbookStatus,
            uv.Status AS VideoStatus,      
            c.ID AS ClassID,
            uc.CourseID

I get the following result

UserId                               Title                                              Grade       WorkbookStatus VideoStatus ClassID     CourseID
------------------------------------ -------------------------------------------------- ----------- -------------- ----------- ----------- -----------
6663227D-8208-44C9-93FC-BC5EE1DF14A4 Introduction to the Business Subscription          100         0              1           1000        1000
6663227D-8208-44C9-93FC-BC5EE1DF14A4 Starting Your Business                             58          0              1           1001        1000
6663227D-8208-44C9-93FC-BC5EE1DF14A4 System Certification                               0           0              0           1002        1001
6663227D-8208-44C9-93FC-BC5EE1DF14A4 Setting Up Your Business                           NULL        0              NULL        1003        1000

(4 row(s) affected)

What i am looking for is for the null value on the bottom row to be converted to a 0
0
Comment
Question by:rodmjay
2 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 16877714
SELECT  au.UserId,
          au.UserName,
          c.Title,
          ut.Grade,
          Case WHEN ua.status IS NULL THEN '0'
                              else ua.status
          End as WorkbookStatus,
          uv.Status AS VideoStatus,    
          c.ID AS ClassID,
          uc.CourseID
FROM         dbo.aspnet_Users AS au INNER JOIN
0
 
LVL 5

Accepted Solution

by:
bwdowhan earned 250 total points
ID: 16877723
Hi rodmjay,

If you are just trying to eliminate NULL values, use the ISNULL function for all values returned from tables that are left outer joined:

SELECT  au.UserId,
        au.UserName,
        c.Title,
        ISNULL(ut.Grade,0) AS Grade,
        ISNULL(ua.status,0) AS WorkbookStatus,
        ISNULL(uv.Status,0) AS VideoStatus,    
        c.ID AS ClassID,
        uc.CourseID
FROM    dbo.aspnet_Users AS au
        INNER JOIN dbo.Users_Courses AS uc
            ON uc.UserID = au.UserId
        inner JOIN dbo.Classes AS c
            ON c.CourseID = uc.CourseID
        left outer JOIN dbo.Users_Tests AS ut
            ON ut.TestID = c.ID
           AND ut.UserID = au.UserId  
        left outer JOIN dbo.Users_Assignments AS ua
            ON ua.AssignmentID = c.ID
           AND ua.UserID = au.UserId
        left outer JOIN dbo.Users_Videos AS uv
            ON uv.VideoID = c.ID
           AND uv.UserID = au.UserId
where   au.username = 'nate'

Brian
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 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

18 Experts available now in Live!

Get 1:1 Help Now