I am trying to return the fields that have intersecting fields for a specific person. What I mean by this is
Name Friend
---- -----
Joe Sally
Joe Bill
Mary Sally
Mary Michael
Mike Joe
Bill Bill
Bill Sally
Gil Sally
Gil Bill
Gil David
Say, we want the list of people that match Joe's second column, they would have to match Sally and Bill both. So only Bill matches this criteria because Mary has one, but she doesn't have Bill. Gil has Sally and Bill, but he also has David. So only Bill should be returned. I was thinking something with INTERSECT would work because that returns common fields but that wouldn't account for someone having more, I think. Not sure how to write a SQL query to do what I want.
Clearly stated, the list of names that have the same exact friends as Joe.
SELECT p2.name
FROM people AS p1
JOIN people AS p2 ON p2.number = p1.number
AND p2.name <> p1.name -- exclude self-join
WHERE p1.name = 'Joe'
AND NOT EXISTS (
SELECT 1
FROM people p3
WHERE p3.name = p2.name
AND p3.number <> p1.number
)
GROUP BY p2.name
HAVING count(*) = (SELECT count(*) FROM people WHERE name = 'Joe')
The last condition AND NOT EXISTS ... is only needed if you want to exclude people that have additional friends Joe does not have.
It excludes Gil in your example from the result.
This is a special case of relational division (with a self-referencing table). You can find a whole arsenal of query techniques in this related answer:
How to filter SQL results in a has-many-through relation
If there can be duplicates (like in your first draft of the question), things get a little more complicated:
WITH p AS (
SELECT name, number, count(*) AS ct
FROM people
GROUP BY name, number
)
SELECT p2.name
FROM p AS p1
JOIN p AS p2 ON p2.number = p1.number
AND p2.ct = p1.ct
AND p2.name <> p1.name -- exclude self-join
WHERE p1.name = 'Joe'
AND NOT EXISTS (
SELECT 1
FROM p p3
WHERE p3.name = p2.name
AND p3.number <> p1.number
)
GROUP BY p2.name
HAVING count(*) = (SELECT count(*) FROM p WHERE name = 'Joe')