CODEWITHSUNDEEP
sqlsql-server
arken far
arken far

Reputation: 1

Using ISNULL in SQL LEFT JOIN to check if the result is null, and if it is, use another value to join

I have a SQL select with a where clause where i want to check if the result is null, and if it is null I want to use another value in the where clause, but i get 0 rows results, even though i know i should get a row as result.

Heres my (updated) SQL code:

DECLARE @LanguageCode NVARCHAR(3);
SET @LanguageCode = 'FR'
SELECT 
wi.WorkItemId,
ds.DisplayString AS Team
FROM dbo.WorkItem AS wi 
LEFT JOIN dbo.DisplayString AS ds ON ds.ElementID = wi.TierId AND ds.LocaleID = ISNULL(@LanguageCode, 'ENU')

The code above returns data for "@LanguageCode" when there is data to return, but it does not switch to use 'ENU' when there is no data. Thats the problem!

This is also just a sample since this is part of a larger query with lots of left joins where i need the same functionality against "LocaleID". I'm hoping there would be something easy solution to this like the code above.

To clarify what i want to achieve, if the c.LocaleID = @LanguageCode returns null rows i want to use the hardcoded value as in c.LocaleID = ENU.

If i don't use the ISNULL function and only use 'ENU' it returns the expected result.

I would appreciate any help. Thanks.

Upvotes: 0

Views: 1456

Answers (3)

VBoka
VBoka

Reputation: 9083

I believe you are looking for something like this:

SELECT *
FROM table_name c
WHERE c.LocaleID  = 
      case when (select count(*) 
                 from table_name tn
                 where tn.LocaleID = @LanguageCode) = 0 then
          'ENU'
      else
           @LanguageCode
      end;

Here is the demo.

Here is a new demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=624100ee44f89decc4c6383e92d0a016

In the new demo I have added some more code to show how to use it in left join as a part of the join condition and also I have added the code to show how to use it in where clause when joining two tables and I believe it belongs in the where clause...

Upvotes: 0

SteveC
SteveC

Reputation: 6015

I think you're looking for this

select coalesce((SELECT rowID FROM dbo.column1 where c.rowID = '1234-1234-1234' and LocaleID = @LanguageCode),
                (SELECT rowID FROM dbo.column1 where c.rowID = '1234-1234-1234' and LocaleID = 'ENU'));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

If I understand correctly, you want one row, either with the specified language code or 'ENU'. If so, use filtering and ORDER BY:

SELECT TOP (1) c.*
FROM dbo.column1 c 
WHERE c.rowID = '1234-1234-1234' AND 
      c.LocaleID IN (@LanguageCode, 'ENU')
ORDER BY (CASE WHEN c.LocaleID = @LanguageCode THEN 1 ELSE 2 END)

Upvotes: 1

Related Questions