I am new to SQL so please bear with me.
I have 2 tables:
Table1:
Col1 Col2 ColXX ColYY IsDisplay
---------------------------------------------
NULL AA NULL AA 1
BBB CCC QQ QQ 1
DDD EEE QQ QQ 1
1F 2G WW WW 0
1H 1H SS NULL 1
Table2:
ID Col1 Col2 Sold Total TotalPax
-------------------------------------------------------
1 BBB CCC 10 10 1
1 DDD EEE 10 10 1
1 1H 1H 5 50 10
1 NULL AA 5 50 10
As you can see, in the 2 tables the only common columns are Col1 and Col2. Given that.
I need to get the unique data for ColXX and ColYY in table1 that are not present in table2. For this case:
ColXX ColYY
----------------
QQ QQ
WW WW
SS NULL
I don't know how to do it. Can somebody can help me? Thanks
Sample data: http://sqlfiddle.com/#!18/92640/15
CodePudding user response:
Use LEFT JOIN
and IS NULL
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
WHERE t2.id IS NULL
Taking a closer look to your data and you example I think you need this:
SELECT DISTINCT ColXX, ColYY
FROM table1 t1
LEFT JOIN table2 t2 ON
ISNULL(t1.ColXX, '') = ISNULL(t2.Col1, '')
AND ISNULL(t1.ColYY, '') = ISNULL(t2.Col2, '')
WHERE ISNULL(t2.ID, '')=''
CodePudding user response:
SELECT
*
FROM
table1 AS t1
WHERE
NOT EXISTS (
SELECT *
FROM table2 AS t2
WHERE (t2.Col1 = t1.ColXX OR (t2.Col1 IS NULL AND t1.ColXX IS NULL))
AND (t2.Col2 = t1.ColYY OR (t2.Col2 IS NULL AND t1.ColYY IS NULL))
)
Note, your fiddle/example has Co11
instead of Col1
?