CODEWITHSUNDEEP
sql-server
Nightmaresux
Nightmaresux

Reputation: 538

Find new values in new table

I Have Table NEW:

Customer   Date      Id
1          201401     9
1          201401     1
1          201402     2
2          201404     3
2          201404     4
3          201406     5
4          201408     6

And table OLD:

Customer   Date      Id
1          201401     1
1          201402     2
2          201404     3
2          201404     4
3          201406     5
4          201408     6

Table NEW has all values from table OLD plus some new ones that are not in table OLD.

I need to find Customer that has same Key (column Customer) and same Date (Date) in both tables (OLD and NEW) and different ID in those tables.

So the SELECT should return in this case only 

1          201401     9

I tried simple join :

SELECT *
FROM NEW n 
LEFT JOIN OLD o ON n.ID = o.ID
WHERE n.Date = o.Date 
AND n.ID <> o.ID

but its obviously wrong because of the condition for the join. But I can't join it on Customer so how can i do it some other way?

Thank you

Upvotes: 0

Views: 28

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460238

You could use EXISTS/NOT EXISTS:

SELECT n.*
FROM NEW n 
WHERE EXISTS(
   SELECT 1 FROM Old o
   WHERE n.Customer = o.Customer AND  n.Date = o.Date
   AND   n.ID <> o.ID
)
AND NOT EXISTS(
   SELECT 1 FROM Old o
   WHERE n.Customer = o.Customer AND  n.Date = o.Date
   AND   n.ID = o.ID
)

Demo

Upvotes: 1

Related Questions