CODEWITHSUNDEEP
sql-serversql-server-2012
user2625842
user2625842

Reputation: 69

Find matching Rows in same MS SQL table

I need to find all rows with the same address using Last as the criterion, however, if someone lives at the same address with a different last name I need them returned too.

Simplified version of my table below

ID      Last     Addr
----    ----    -----
1      Smith    123 Fake St
2      Smith    123 Fake St
3      Fox      123 Fake St
4      Jones    111 Jones Rd

I tried this query

SELECT *
FROM Table
WHERE Addr IN
         (SELECT Addr
          FROM Table AS T
          GROUP BY Addr
          HAVING COUNT(ID) > 1)
       AND Last = 'Smith'

This returns IDs 1 and 2 but I also need it to return ID 3 because the address is the the same. I understand it isn't returning because Last doesn't match in my WHERE statement, however, I would only have Last when I search and need to know everyone else at the same address regardless of their name.

Upvotes: 0

Views: 651

Answers (3)

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

Please try the following:

SELECT *
FROM Table AS t
WHERE EXISTS (SELECT 1 
                FROM Table 
               WHERE Addr = t.Addr AND Last = 'Smith');

Here is a running sample:

declare @Table table (ID int, [Last] nvarchar(50), Addr nvarchar(50));

insert into @Table values
(1, 'Smith', '123 Fake St'),
(2, 'Smith', '123 Fake St'),
(3, 'Fox', '123 Fake St'),
(4, 'Jones', '111 Jones Rd');

SELECT *
FROM @Table AS t
WHERE EXISTS (SELECT 1 
                FROM @Table 
               WHERE Addr = t.Addr AND Last = 'Smith');

/* Result
ID Last     Addr
-- -------- -----------------------
1  Smith    123 Fake St
2  Smith    123 Fake St
3  Fox      123 Fake St
*/

Upvotes: 0

chungtinhlakho
chungtinhlakho

Reputation: 930

You can do a count and group by, anything with more than 1 is a repeat

select ID, Last, Addr, count(1) num from table group by ID, Last, Addr

Upvotes: 0

Lamak
Lamak

Reputation: 70638

You can use COUNT(*) OVER():

WITH CTE AS
(
    SELECT *,
           N = COUNT(*) OVER(PARTITION BY Addr)
    FROM dbo.YourTable
)
SELECT *
FROM CTE A
WHERE N > 1
AND EXISTS(SELECT 1 FROM dbo.YourTable
           WHERE Last = 'Smith'
           AND Addr = A.Addr);

Upvotes: 1

Related Questions