CODEWITHSUNDEEP
sqlms-access
Alex
Alex

Reputation: 6645

Returning only records that have matching fields in other records

I have a query that returns a list of customers and their addresses. 

ID  FName   LName   Address1        City    Postcode       
--------------------------------------------------------
1   James   Smith   1 Bank Street   London  W1C 1AA
2   Sarah   Jones   45 Moor Ave     London  SW1 1YH
3   Mary    Smith   1 Bank Street   London  W1C 1AA
4   Sean    Baker   17 White Blvd   London  SE3 7TH
5   Bob     Patel   58B Canal St    London  NW2 2TT
6   Seeta   Patel   58B Canal St    London  NW2 2TT
7   David   Hound   4 Main St       London  E11 8AB

I'm trying to produce another query from this data that selects a list of customers who are related/living together.The criteria for this would be the same Address 1 and Postcode fields.

My question is how I can produce a query that only selects records that have at least 1 other record with matching [Address1] and [Postcode]? ie; in the above example return only records 1, 3, 5 and 6.

Upvotes: 2

Views: 72

Answers (1)

Fred
Fred

Reputation: 5808

Select * From 
Customers c JOIN 
    (SELECT Address1, PostCode FROM Customer GROUP BY Address1, PostCode HAVING Count(1) > 1) c2 
        ON c.Address1 = c2.Address1 AND c.PostCode = c2.PostCode

Upvotes: 4

Related Questions