Comparing tables

[daevux]

I have 2 tables (A and B). A has 3 fields (City, State, Phone) and B has 2 (City, State). I need to find records that exist in A but not B, ignoring the Phone field, since it doesnt exist in table B. For instance:

Table A:
City     |  State   |   Phone
-----------------------------------
Atlanta      GA         7707877777
Memphis      TN         2017823733
Dallas       TX         2936351212
LA           CA         7364928377

Table B:
City     |  State 
--------------------  
Boston       MA   
Dallas       TX  
San F.       CA   
Atlanta      GA  


Result:

City     |  State   |   Phone
-----------------------------------
Memphis      TN         2017823733
LA           CA         7364928377

Keep in mind that table B has 400,000 records and table A has 8,000 records. Also, I have simplified the tables a great deal (meaning these fields are the only ones that I am concerned about). Thanks a bunch.

 

[tlbroadbent]

Try this.

Select City, State, Phone
From tableA
Where Not Exists
(Select * From tableB
Where city=tableA.city
And state=tableA.state) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.