Hi All,I'd like to know if there is a better way of producing my result set rather than using the below union:CREATE TABLE #t1(PKT1 INT IDENTITY (10,1),ID INT,ID2 INT)CREATE TABLE #t2 (PKT2 INT IDENTITY (20,1),ID INT,ID2 INT)INSERT #t1 ( ID, ID2 )SELECT 18185, NULL UNION ALLSELECT 18165, 39875 UNION ALLSELECT NULL, 39875 UNION ALLSELECT NULL, 39875 UNION ALLSELECT 18189, NULL UNION ALLSELECT NULL, 40001 UNION ALLSELECT 18163, NULL UNION ALLSELECT 18495, 40011 INSERT #t2 ( ID, ID2 )SELECT 18185, NULL UNION ALLSELECT NULL, 39875 UNION ALLSELECT 18189, 63751 UNION ALLSELECT 18495, 40011 SELECT * FROM #t1 SELECT * FROM #t2 SELECT *FROM #t1INNER JOIN #t2 ON #t1.ID = #t2.IDWHERE #t2.ID2 IS NULL UNION ALL SELECT *FROM #t1INNER JOIN #t2 ON #t1.ID2 = #t2.ID2WHERE #t2.ID IS NULL UNION ALL SELECT *FROM #t1INNER JOIN #t2 ON #t1.ID = #t2.ID AND #t1.ID2 = #t2.ID2
I need to join ID1 and ID2 to t2 only when one of them exists in t2, then when both ID1 and ID2 exist in t2.Thanks in advance