How to return rows that do not yet exist in another table without common ID

user_17334611 0
Viewed 55

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

tsql sql-server sql
2 Answers

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, '')=''
user_45238960 
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?

Demo : http://sqlfiddle.com/#!18/92640/12

Dems_533410
Related