Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Comparing tables

Status
Not open for further replies.

daevux

Programmer
Joined
May 18, 2002
Messages
4
Location
US
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:
Code:
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.
 
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:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top