sql - Find groups with matching rows

Question

I have a table of people (CarOwners) and the types of cars they own

+-------+-------+
| Name  | Model |
+-------+-------+
| Bob   | Camry |
| Bob   | Civic |
| Bob   | Prius |
| Kevin | Civic |
| Kevin | Focus |
| Mark  | Civic |
| Lisa  | Focus |
| Lisa  | Civic |
+-------+-------+

Given a name, how do i find other people with the exact same cars? For example, if i'm targeting Mark, no one else has ONLY a Civic so the query would return nothing. If i'm targeting Lisa, the query would return

+-------+-------+
| Name  | Model |
+-------+-------+
| Kevin | Civic |
| Kevin | Focus |
+-------+-------+

Because Kevin has the exact same cars as Lisa. If i targeted Kevin, the query would return Lisa.

I created a cte which contains my target persons cars, but i'm not sure how to implement the "exact match" requirement. All of my attempts return results with subset matches.

with LisaCars as (
    SELECT Model FROM CarOwners WHERE Name = 'Lisa'
)
SELECT Name, Model
FROM CarOwners
WHERE Model in (SELECT * FROM LisaCars) AND Name != 'Lisa'

This query would return all people who have either a Civic or a Focus, which isn't what i'm looking for.

+-------+-------+
| Name  | Model |
+-------+-------+
| Bob   | Civic |
| Kevin | Civic |
| Kevin | Focus |
| Mark  | Civic |
+-------+-------+

Answer 1

This counts the number of rows for each name using a common table expression(cte) with count() over().

Then the matches cte uses a self-join where the names do not match, the models match, the count of models for each name match, and one of those names is 'Lisa'. The having clause ensures that count of matched rows (count(*)) matches the number of models that name has.

matches itself would only return the name of each person, so we join back to the source table t to get the full list of models for each match.

;with cte as (
  select *
    , cnt = count(*) over (partition by name)
  from t
)
, matches as (
  select x2.name
  from cte as x 
    inner join cte as x2
       on x.name <> x2.name
      and x.model = x2.model
      and x.cnt   = x2.cnt 
      and x.name  = 'Lisa'
  group by x2.name, x.cnt
  having count(*) = x.cnt
)
select t.* 
from t
  inner join matches m
    on t.name = m.name

rextester demo: http://rextester.com/SUKP78304

returns:

+-------+-------+
| name  | model |
+-------+-------+
| Kevin | Civic |
| Kevin | Focus |
+-------+-------+

---

We could also write it without the ctes, but it makes it a little harder to follow:

select t.*
from t 
  inner join (
    select x2.Name
    from (
      select *, cnt = count(*) over (partition by name) 
      from t 
      where name='Lisa'
      ) as x
      inner join (
      select *, cnt = count(*) over (partition by name) 
      from t
      ) as x2
        on x.name <> x2.name
       and x.model = x2.model
       and x.cnt   = x2.cnt 
    group by x2.name, x.cnt
    having count(*) = x.cnt
  ) as m 
    on t.name = m.name