Solved

Confusing Case Statement w/ Left Join

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

815 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

11 Experts available now in Live!

Get 1:1 Help Now