Help with a query - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

Help with a query

Author

Topic

sql_quiz
Starting Member

2 Posts

Posted - 2014-11-06 : 15:01:32

I have a table containing mailing addresses. I want to create a query which pulls all records that have duplicate street name (street number and street name are separate fields) and zip code matches.

If there are 3 addresses on Jackson Street in 40207, I want to pull all the fields of those three rows. If there's only one address on Hancock Street in 40204, I want to ignore that one.

I've tried using HAVING (COUNT(streetname) >1). I've been working on this since yesterday morning and haven't found quite what I need through searches.

Is there any easy way to do this in one query? Any help is greatly appreciated!

The fields are:
lastName
firstName
streetNum
streetName
city
state
zip

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-06 : 15:29:14

something like this:


select lastname, firstname, streetnum, m1.streetname,city, state,m1.zip
from mytable m1
join (
   select streetname, zip
   from mytable m2
   group by streetname, zip
   having count(*) > 1
) m2
on m2.streetname = m1.streetname
and m2.zip  = m1.zip

sql_quiz
Starting Member

2 Posts

Posted - 2014-11-06 : 16:13:28

Thank you very much! I'll start with that and tinker if necessary.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources