?
Solved

Confusing Case Statement w/ Left Join

Posted on 2006-06-10
2
Medium Priority
?
226 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 1000 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 1000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

839 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