Search code examples
sqlsql-servercoalesce

Simplify COALESCE query

I'm looking to simplify the query below and I just can't wrap my head around an alternate solution but I think there must be one available.

The objective is to retrieve Col_D from Col_C. If Col_D returns NULL, I want to get Col_D from Col_B. If that returns NULL, once again go up the ladder and get Col_D from Col_A. But only where Col_B and Col_C are NULL.

Tables

Table_A
Col_A   Col_B   Col_C   Col_X
L000    3200    3220    0000

Table_B
Col_A   Col_B   Col_C   Col_D
L000    NULL    NULL    3256
L000    3200    NULL    6483
L000    3200    3210    7213
L000    3200    3220    NULL
L000    3200    3230    9462

Query

DECLARE @X nvarchar(4) = '0000'

DECLARE @A nvarchar(4), @B nvarchar(4), @C nvarchar(4)
SELECT @A = Col_A, @B = Col_B, @C = Col_C FROM [Table_A] 
WHERE [Col_X] = @X

SELECT COALESCE(
(SELECT [Col_D] FROM [Table_B] WHERE Col_C = @C AND Col_B = @B AND Col_A = @A), 
(SELECT [Col_D] FROM [Table_B] WHERE Col_C IS NULL AND Col_B = @B AND Col_A = @A), 
(SELECT [Col_D] FROM [Table_B] WHERE Col_C IS NULL AND Col_B IS NULL AND Col_A = @A)
) AS Col_D

Expected Output

Col_D
6483

Edit: Added Table_A and Expected Output

Solution

  • SQL Server sorts nulls last when ordering descending. So all you have to do is get all possible candidate records, which is: col_d must not be null, col_a must match, and col_b and col_c must match or be null. Order descending and take the top row.

    select col_d
from table_b
where col_d is not null
and col_a = @a
and (col_b = @b or col_b is null)
and (col_c = @c or col_c is null)
order by col_b desc, col_c desc;

    Full query including table_a:

    select b.col_d
from (select * from table_a where col_x = @x) a
join table_b b on  b.col_d is not null
               and b.col_a = a.col_a
               and (b.col_b = a.col_b or b.col_b is null)
               and (b.col_c = a.col_c or b.col_c is null)
order by b.col_b desc, b.col_c desc;