Solved

Confusing Case Statement w/ Left Join

Posted on 2006-06-10
2
218 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

920 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

15 Experts available now in Live!

Get 1:1 Help Now