Needa Query to filter the matching records

Question

Needa Query to filter the matching records

sugan

SSC-Addicted

Points: 492
More actions
September 12, 2010 at 10:58 pm

#98900

Need a Query to filter the matching records from table.
Having 2 tables. Table A Having 100 records and Table B having 20 records which the same record which is in Table A.
Need a Query to fetch the records from Table A and to eliminate the records which having TableB. Finally i need only 80 records from table A. I can't use NOT IN function. I need to comapre 5 column in both tables.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

RakeshRSingh SSCarpal Tunnel Points: 4109 More actions · Answer 1

Try using Delete with Joining TableA and TableB with the 5 columns you have to check for match.

Please consider you condition for matching with OR and AND, whether all the five columns need to match or any one will do.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Try NOT EXISTS. That'll let you join on your 5 columns.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Left join table B to table a. Nothing could be simpler.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gofrancesc Hall of Fame Points: 3269 More actions · Answer 4

You said you must compare 5 fields. Maybe they are the joining key?, try it:

SELECT A.* FROM A

LEFT JOIN B

ON A.Field1 = B.Field1

AND A.Field2 = B.Field2

AND A.Field3 = B.Field3

AND A.Field4 = B.Field4

AND A.Field5 = B.Field5

WHERE B.Field1 IS NULL

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 5

try using EXCEPT

select 5 columns from table A

except

select 5 columns from table B