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

Time:11-05

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?

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

  • Related