CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
Captain16
Captain16

Reputation: 327

SQL filter the data if existed in the other table

I'm using SQL Server 2005, and I have a script like this:

  select INV_Nr, INV_Date, INV_Customer

  from INVOICE A, 
  left outer join CANCEL_INVOICE B on B.INV_Nr  = A.INV_Nr

So how can I add in 'where' clause / filter that all the INVOICE.INV_Nr that existed in CANCEL_INVOICE.INV_Nr will not show in the query result?

Thanks,

Upvotes: 1

Views: 1622

Answers (2)

Sunil Naudiyal
Sunil Naudiyal

Reputation: 334

Try this!!

It show all those invoice A.INV_Nr which is not exist in table CANCEL_INVOICE 

  SELECT INV_Nr, INV_Date, INV_Customer
  FROM INVOICE A, 
  LEFT OUTER JOIN CANCEL_INVOICE B ON A.INV_Nr=B.INV_Nr
  WHERE B.INV_Nr IS NULL

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460168

One way(probably the best), NOT EXISTS:

SELECT inv_nr, 
       inv_date, 
       inv_customer 
FROM   invoice i 
WHERE  NOT EXISTS(SELECT 1 
                  FROM   cancel_invoice c 
                  WHERE  c.inv_nr = i.inv_nr)

The LEFT OUTER JOIN approach might work but is less efficient and leads to incorrect (or at least unexpected) results, since there is no way to differentiate between a row that doesn't exist and a row that does exist but where that column is NULL.

Upvotes: 4

Related Questions