Hi,
Use below sql
DECLARE @t1 TABLE(col1 INT, col2 INT)
DECLARE @t2 TABLE(col1 INT, col2 INT)
INSERT INTO @t1(col1,col2)
SELECT 1,2
UNION ALL SELECT null, 10
UNION ALL SELECT 3,4
UNION ALL SELECT 89,null
UNION ALL SELECT 5,6
INSERT INTO @t2(col1,col2)
SELECT 3,2
UNION ALL SELECT 9,null
UNION ALL SELECT 5,4
UNION ALL SELECT 7,6
UNION ALL SELECT null,87
SELECT col1,col2 FROM @t1
SELECT col1,col2 FROM @t2
SELECT CASE WHEN ISNULL( a.col2,-1) =-1 THEN a.col1
ELSE a.col2 END ,
( SELECT col1 FROM @t2 WHERE col2 = CASE WHEN ISNULL( a.col2,-1) =-1 THEN a.col1
ELSE a.col2 END )
FROM @t1 a
Logic:
select col2 if col2 is null then select col1 from t1 then
select col1 from t2 where t2.col2 = t1.selected value