CODEWITHSUNDEEP
sql-server-2008
mmssaann
mmssaann

Reputation: 1507

get records of a table doesnt exists in another table + sql server

I have two tables as below:

Table A:

AIID  AID  AName
1     200   'A'
2     200   'B'
3     200   'C'
4     300   'D'
5     300   'E'
6     400   'F'
7     400   'G'

Table B:

BID  AIID   AID   AName
 1     2     200    'B'
 2     6     400    'F'

Now I need to get the records from Table A, all AIIDs by AID wise, whose AIID is greater than AIID's in table B by AID wise, also any new AID records.

Ex, I need to get the below rows from Table A:

AIID  AID  AName
3     200   'C'
4     300   'D'
5     300   'E'
7     400   'G'

Can some body advise how to write query for this?

Thanks,

Upvotes: 0

Views: 39

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

As far as I understand your problem, this should do it;

SELECT TableA.*
FROM TableA
LEFT JOIN TableB
  ON TableA.AID  = TableB.AID
 AND TableA.AIID <= TableB.AIID
WHERE TableB.BID IS NULL;

It basically does the reverse, it finds all entries in A that have a larger or equal entry in B and eliminates them. The rest are returned.

An SQLfiddle to test with.

Upvotes: 1

Related Questions