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

[code]
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]

See my reply in thread183-321270. Avoid posting in multiple forums as much as possible. It can be very frustrating and irritating 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.

 

[Guest_imported]

I supsect you want an EXCEPTION JOIN (RIGHT OUTER JOIN) or whatever it is called in your RDBMS .
That is, if this join type is supported at all ...

Something like

SELECT a.city, a.state, a.phone
FROM tableA as a
EXCEPTION JOIN tableB as b ON a.city = b.city
AND a.state = b.state

should give you all rows from tableA where there is no match on tableB by city and state .

If this join type is not supported in your RDBMS you can use a workaround like

SELECT a.city, a.state, a.phone
FROM tableA as a
LEFT OUTER JOIN tableB as b ON a.city = b.city
AND a.state = b.state
WHERE b.city IS NULL
AND b.state IS NULL

Good luck with this
milan432

 

[ThomVF]

In ORACLE:

sslect * from table_a
where (city,state) in (
select city, state
from table_a
MINUS
select city, state
from table_b
);

Gives all rows from A that are in A, but are not in B. =================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com