Comparing 2 tables for unmatched records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing 2 tables for unmatched records

Author

Topic

Sas1
Starting Member

2 Posts

Posted - 2011-08-08 : 11:28:26

I have 2 tables which only link on fields like housenumber, street, city. There is no other way to link the tables but thru their addresses.

One table has 4 records for a particular address and the other table is showing only 1 record (i.e.
Table 1 is GISAddrPt Table 2 is HanGISAddr
201 Jane St Cleveland 201 Jane St Cleveland
201 Jane St Cleveland
201 Jane St Cleveland
201 Jane St Cleveland

I need to create a query that can identify the 3 extra records for 201 Jane St Cleveland that are in Table 1 but not in Table 2.

I have tried the sql below but it is not identifying the 3 extra records that I am looking for.

select *
from dbo.GISAddrPt GIS

where not address in (

select address from
dbo.GISAddrPt GIS
inner join
dbo.HanGISAddr HA
on
GIS.PreSuffix = HA.PREDIR and
GIS.HouseNum = HA.STNO and
GIS.Street = HA.STNAME and
GIS.Suffix = HA.SUFFIX and
GIS.PostSuffix = HA.POSTDIR and
GIS.Suite = HA.STSUB and
GIS.City = HA.CITY and
GIS.Zip = HA.ZIP
)
order by address

Anyone willing to help? I certainly would appreciate some guidance pointing me in the right direction. Thanks in advance.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-08 : 11:53:16

DECLARE @GISAddrPt Table (addr1 varchar(100))
DECLARE @HanGISAddr TABLE (addr2 varchar(100))

INSERT INTO @GISAddrPt
SELECT '201 Jane St Cleveland' UNION ALL
SELECT '201 Jane St Cleveland' UNION ALL
SELECT '201 Jane St Cleveland' UNION ALL
SELECT '201 Jane St Cleveland'

INSERT INTO @HanGISAddr
SELECT '201 Jane St Cleveland'

select g.addr1 ,count(*) -1
from @GISAddrPt g

where exists
(select 1 from @HanGISAddr h where g.addr1 = h.addr2 <all the joins go here>)

group by g.addr1

Jim

Everyday I learn something that somebody else already knew

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-09 : 01:06:18

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data (you did not), avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names (you do not). Please tell us if you can change the DDL.

>> I have 2 tables which only link [sic: joins are not links] on fields [sic: columns are not fields] like house number, street, city. There is no other way to link [sic] the tables but thru their addresses. <<

You got the basic terms wrong; you are still back in a network database world where there are links, fields and records. Start thinking in sets and SQL is easy. Remember set difference from your Junior High School math class?

SELECT X.street_prefix, X.house_nbr, X.street_name, X.street_suffix, X.suite_nbr,
X.city_name, X.zip ,
COUNT(*) AS extra_copy_cnt
FROM (SELECT street_prefix, house_nbr, street_name, street_suffix, suite_nbr, city_name, zip
FROM Oversize_Address_List
EXCEPT
SELECT street_prefix, house_nbr, street_name, street_suffix, suite_nbr, city_name, zip
FROM Address_List
AS X (street_prefix, house_nbr, street_name, street_suffix, suite_nbr, city_name, zip)
GROUP BY X.street_prefix, X.house_nbr, X.street_name, X.street_suffix, X.suite_nbr, X.city_name, X.zip ;

If the tables used the same structure and column names, the code would be cleaner suing SELECT *.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Sas1
Starting Member

2 Posts

Posted - 2011-08-10 : 11:54:13

Thank you jimf for your response. I was able to use it succcessfully.
I am glad to see a response that assisted me in learning.
Sometimes people are asked to do things in their jobs that are unfamiliar to them because the expert left the company.
I tried to explain the question and provided an example of my best attempt to resolve the issue.

Considering I was joining the forum specifically for people that are working with SQL Server Programming for the first time, I
took issue with jcelko's response. It seems from the response of others, I am not alone in my feeling.

If we need to follow standard's, perhaps this gentlemen does as well.
I'm looking to learn, not looking for condescension and a response that starts out with a canned response 'We can not read minds'.

Sas

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-10 : 12:08:10

Glad I could help! Don't worry about Joe Celko. The only thing Joe Celko hates more than Joe Celko is people who are not Joe Celko. Despite his atrocious grammar and general unpleasantness, he actually has good advice. Try to filter out his insults and see if you can use the good advice. Most people are pretty friendly here.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources