Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Confusing Case Statement w/ Left Join

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

618 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