There is a code that I'm trying to improve that looks like this(simple example):
SELECT DISTINCT a.col_a
,COALESCE(b1.col_c, b2.col_c, b3.col_c)
FROM tab_a a
LEFT JOIN tab_b b1
ON a.col_a = b1.col_a
AND b1.col_b = 'blabla1'
LEFT JOIN tab_b b2
ON a.col_a = b2.col_a
AND b2.col_b = 'blabla2'
LEFT JOIN tab_b b3
ON a.col_a = b3.col_a
AND b3.col_b = 'blabla3';
You can use the following script to recreate those tables
CREATE TABLE tab_a(col_a int)
CREATE TABLE tab_b(col_a INT, col_b VARCHAR(10), col_c INT)
INSERT INTO dbo.tab_a ( col_a ) VALUES ( 1 ), ( 2 ), ( 3 );
INSERT INTO dbo.tab_b ( col_a
,col_b
,col_c )
VALUES ( 1, 'blabla1', 1 )
,( 1, 'blabla2', 3 )
,( 1, 'blabla2', 5 )
,( 2, 'blabla2', NULL )
,( 2, 'blabla3', 5 );
How can I change it to 1 join + maybe window function and how to rewrite coalesce part. Just to explain, current plan shows 3 tab_b scans, I want to reduce it to 1.