CODEWITHSUNDEEP
sqlsql-server-2005
ImGreg
ImGreg

Reputation: 2983

SQL LEFT JOIN with a CASE Statement

I'm using SQL Server 2005 and trying to build a query in a way that I can't seem to get working.

I have two tables. One table has a list of ItemTypes and another with a list of mappings between ItemTypes and users.

I want to have a query that will return these results:

ID   | ItemType      | Enabled?
---------------------------------
1    | Type1         | false
2    | Type2         | true

The Enabled column is going to be based on whether there is an entry in the other table. I want to use some sort of case statement where if the LEFT JOIN item exists, then put TRUE otherwise put FALSE.

SELECT systype.ID, systype.TypeDescription, 
'Enable' = CASE hid.ID WHEN NULL THEN 'true' ELSE 'false' END
FROM [HardwareTestcaseManagement].[dbo].[A7_SystemItemTypes] systype
LEFT JOIN [dbo].[A7_HiddenNewsFeedTypes] hid 
ON systype.ID = hid.SystemItemTypesID
AND hid.UserName = @UserName

However, this query I've come up with so far ^ is not working quite as I intended.

Any suggestions how I can tweak this query to work how I want it to?

EDIT: When I do a left join and just select the column "hid.ID" it comes back as either NULL or the actual ID. I was hoping I could modify the value based on whether it was null or not.

Upvotes: 1

Views: 13465

Answers (6)

th1rdey3
th1rdey3

Reputation: 4358

Just another way of doing it without using CASE

'Enable' = isnull(nullif(isnull(hid.id,'false'),hid.id),'true')

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

A simple and efficient approach is to use EXISTS instead of a join:

SELECT systype.ID, systype.TypeDescription, 
       [Enable] = CASE WHEN EXISTS( 
                     SELECT 1 FROM A7_HiddenNewsFeedTypes ft
                     WHERE ft.SystemItemTypesID = systype.ID
                     AND ft.UserName = @UserName
                  ) THEN 'true' ELSE 'false' END
FROM [HardwareTestcaseManagement].[dbo].[A7_SystemItemTypes] systype

Upvotes: 3

Kaf
Kaf

Reputation: 33809

There are two formats of CASE. You need to use Searched Case NOT Simple Case when checking null values;

SELECT systype.ID, systype.TypeDescription, 
      CASE WHEN hid.UserName IS NULL THEN 'false' ELSE 'true' END Enabled
FROM [HardwareTestcaseManagement].[dbo].[A7_SystemItemTypes] systype
LEFT JOIN [dbo].[A7_HiddenNewsFeedTypes] hid 
         ON systype.ID = hid.SystemItemTypesID
         AND hid.UserName = @UserName

Upvotes: 2

offthat
offthat

Reputation: 400

SELECT systype.ID, systype.TypeDescription, 
CASE WHEN hid.SystemItemTypesID IS NULL THEN 'true' ELSE 'false' END as Enable
FROM [HardwareTestcaseManagement].[dbo].[A7_SystemItemTypes] systype
LEFT JOIN [dbo].[A7_HiddenNewsFeedTypes] hid 
ON systype.ID = hid.SystemItemTypesID
AND hid.UserName = @UserName

Upvotes: 1

Guffa
Guffa

Reputation: 700322

You can't compare null values like other values. Use the is null operator in the case:

'Enable' = CASE WHEN hid.ID IS NULL THEN 'true' ELSE 'false' END

Upvotes: 2

vasja
vasja

Reputation: 4792

SELECT DISTINCT systype.ID, systype.TypeDescription, 
'Enable' = CASE
     WHEN hid.UserName = @UserName THEN 'true'
     ELSE 'false'
   END
FROM [HardwareTestcaseManagement].[dbo].[A7_SystemItemTypes] systype
LEFT JOIN [dbo].[A7_HiddenNewsFeedTypes] hid 
ON systype.ID = hid.SystemItemTypesID
-- no join on UserName

Upvotes: 0

Related Questions