Avatar of Allan
AllanFlag for United States of America

asked on 

T-SQL - Get Same Address1 with Different Address2

Hi Experts!

Have the following data and need a query to find the Pid that has same
Address1, but different Address2.
Query should RETURN 1, 4, and 5 -- this is for SQL Server 2012

PiD   Address1          Address2
----- -----------------       --------------
1     123 ABC St         Suite 201
1     123 XYZ St         Suite 201
1     123 Cookie St    Suite 201
1     123 ABC St        Suite 201
1     123 ABC St        Suite 301
2     457 Marvel St    Suite 1001
2     1 DownTown Lane  
3     2 Busy Rd         Apt 2001
3     2 Busy Ave        Apt 20
3     2 Busy Ave        Apt 20
4     10 CutOver Lane   Suite 201
4     10 CutOver Lane   Suite 301
4     10 CutOver Lane   Suite 401
5     10 CutOver Lane   Suite 201
5     10 CutOver Lane   Suite 301


DECLARE @PersonAddress TABLE
(
  PiD int, 
  Address1 varchar(50),
  Address2 varchar(50)
)

INSERT INTO @PersonAddress (PiD, Address1, Address2)
 SELECT 1, '123 ABC St', 'Suite 201' UNION ALL
 SELECT 1, '123 XYZ St', 'Suite 201' UNION ALL
 SELECT 1, '123 Cookie St', 'Suite 201' UNION ALL
 SELECT 1, '123 ABC St', 'Suite 201' UNION ALL
 SELECT 1, '123 ABC St', 'Suite 301' UNION ALL
 SELECT 2, '457 Marvel St', 'Suite 1001' UNION ALL
 SELECT 2, '1 DownTown Lane', '' UNION ALL
 SELECT 3, '2 Busy Rd', 'Apt 2001' UNION ALL
 SELECT 3, '2 Busy Ave', 'Apt 20' UNION ALL
 SELECT 3, '2 Busy Ave', 'Apt 20' UNION ALL
 SELECT 4, '10 CutOver Lane', 'Suite 201' UNION ALL
 SELECT 4, '10 CutOver Lane', 'Suite 301' UNION ALL
 SELECT 4, '10 CutOver Lane', 'Suite 401' UNION ALL
 SELECT 5, '10 CutOver Lane', 'Suite 201' UNION ALL
 SELECT 5, '10 CutOver Lane', 'Suite 301' 

Open in new window


TIA!
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Allan
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try this:
select a.PiD from
(
Select PiD, Address1
from @PersonAddress
group by PiD, Address1
having count(Address1) > 1
) a inner join
@PersonAddress b on a.PiD = b.PiD and a.Address1 = b.Address1
group by a.PiD
having count(distinct b.Address2) > 1

Open in new window

Avatar of Allan
Allan
Flag of United States of America image

ASKER

Thanks Ryan; let me try it out..
Avatar of Allan
Allan
Flag of United States of America image

ASKER

Ryan,

Here's an example where it shouldn't return the PiD,but it did:

DECLARE @PersonAddress TABLE
(
  PiD int, 
  Address1 varchar(50),
  Address2 varchar(50)
)

INSERT INTO @PersonAddress (PiD, Address1, Address2)
SELECT 30, '73-345 Power Pl', 'Suite 200' UNION ALL
SELECT 30, '73-345 Power Pl', 'Suite 200' UNION ALL
SELECT 30, '73-345 Power Pl', 'Suite 200' UNION ALL
SELECT 30, '73-345 Power Pl', 'Suite 200' UNION ALL
SELECT 30, '73-345 Power Pl', 'Suite 200' UNION ALL
SELECT 30, '73-345 Power Pl', 'Suite 200' UNION ALL
SELECT 30, '75-240 Krazy Dr', 'Suite 6B' UNION ALL
SELECT 30, '75-240 Krazy Dr', 'Suite 6B' UNION ALL
SELECT 30, '75-240 Krazy Dr', 'Suite 6B' UNION ALL
SELECT 30, '75-240 Krazy Dr', 'Suite 6B' UNION ALL
SELECT 30, '75-240 Krazy Dr', 'Suite 6B' UNION ALL
SELECT 30, '75-240 Krazy Dr', 'Suite 6B' UNION ALL
SELECT 30, '98-151 Saint St', 'Suite 142' UNION ALL
SELECT 30, '98-151 Saint St', 'Suite 142' UNION ALL
SELECT 30, '98-151 Saint St', 'Suite 142' UNION ALL
SELECT 30, '98-151 Saint St', 'Suite 142' UNION ALL
SELECT 30, '98-151 Saint St', 'Suite 142' 

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Allan
Allan
Flag of United States of America image

ASKER

let me test it out ..
Avatar of Allan
Allan
Flag of United States of America image

ASKER

gracias!
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo