troubleshooting Question

T-SQL - Get Same Address1 with Different Address2

Avatar of Allan
Allan asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL
6 Comments1 Solution133 ViewsLast Modified:
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' 

TIA!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros