Return field in SQL that intersects on a second field?

Question

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.

`they would have to match 1 and 2 both.` Why? I see only `number = 1` for Joe ..? — Erwin Brandstetter, Mar 09 '13 at 02:46
What about `GROUP_CONCAT` and then to `JOIN` it with itself? — PM 77-1, Mar 09 '13 at 02:51
@Erwin Brandstetter - Both Joe and Bill have (`1,1`) combination. — PM 77-1, Mar 09 '13 at 02:54
You still neglected to clarify: can there be duplicates? Can `(Joe, Sally)` appear more than once? Your first draft of the question had duplicates ... — Erwin Brandstetter, Mar 09 '13 at 03:18
no, it would only appear once, their friendship would be the key so the relationship (Joe,Sally) would only appear once — user1490083, Mar 09 '13 at 03:22

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Without duplicates

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

With duplicates

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')

It's not doing anything, really. That just the way you write `EXISTS` (anti-)semi-joins. Consider [this related question](http://stackoverflow.com/questions/7710153/what-is-easier-to-read-in-exists-subqueries). Also, now that you have changed your question, my first query might have been correct to begin with. Your question is just not clear on the exact conditions. Can there be duplicates? Etc. — Erwin Brandstetter, Mar 09 '13 at 03:09
So basically to make it more clear, list of people that have the same exact friends as Joe — user1490083, Mar 09 '13 at 03:19
@user1490083: I added a simpler version for a table without duplicates and fixed a bug in the `GROUP BY` clause - and another bug in the having clause. — Erwin Brandstetter, Mar 09 '13 at 03:40

Return field in SQL that intersects on a second field?

1 Answers1

Without duplicates

With duplicates