CODEWITHSUNDEEP
mysqlsql
JamesNickel
JamesNickel

Reputation: 403

MySQL SELECT FROM a relation table WHERE they have the same relatives

I'm having a situation that there is a relation table like this:

eaters:

id   name     fruit
1    jack     banana
2    jack     apple
3    jane     banana
4    jane     apple
5    jane     orange
6    richard  banana
7    richard  apple

Now, who has ate the same types of fruit as 'Jack'?

For example in above table, 'Richard' is the answer.

The pseudo-query I have in mind is:

SELECT name AS the_guy 
FROM eaters 
WHERE 
  (SELECT fruit FROM eaters WHERE name=the_guy) = 
  (SELECT fruit FROM eaters WHERE name='jack')

And I don't know if this is even possible in MySQL. For now I fetched the entire table and extract the answer from the result with PHP, which is not a nice approach. Any suggestions?

Upvotes: 3

Views: 1493

Answers (4)

iminiki
iminiki

Reputation: 2573

You can write:

SELECT e2.name
FROM   eaters e1
      INNER JOIN eaters e2
              ON e1.fruit = e2.fruit
WHERE  e1.name = 'Jack'
      AND e2.name != 'Jack'
GROUP  BY e2.name

Upvotes: 5

misafer
misafer

Reputation: 61

better use this :

SELECT DISTINCT
       e.name
FROM eaters e
WHERE name <> 'jack'
      AND NOT EXISTS
(
    SELECT DISTINCT
           name
    FROM eaters e1
    WHERE NOT EXISTS
    (
        SELECT * FROM eaters e2 WHERE e2.name = 'jack' AND e1.fruit = e2.fruit
    )
          AND e1.name = e.name
);

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

One way can be using conditional Group_Concat() with Having clause. We can get all the fruits of jack in an ordered comma separated string, within a subquery (Derived Table).

Now, we can get the same ordered list of fruits for other guys, and use HAVING clause to consider those name values which match the "jack_fruits".

Query

SELECT t1.name AS the_guy 
FROM eaters AS t1 
JOIN (SELECT GROUP_CONCAT(fruit ORDER BY fruit) AS jack_fruits
      FROM eaters 
      WHERE name = 'jack') AS t2 
WHERE t1.name <> 'jack' 
GROUP BY t1.name 
HAVING GROUP_CONCAT(t1.fruit ORDER BY fruit) = MAX(t2.jack_fruits );

Result

| the_guy |
| ------- |
| richard |

View on DB Fiddle

Upvotes: 1

forpas
forpas

Reputation: 164089

This is easy to understand:

SELECT DISTINCT name FROM eaters 
WHERE
    name <> 'Jack' 
    AND 
    fruit IN (SELECT fruit FROM eaters WHERE name = 'Jack')

Upvotes: 2

Related Questions