SQL - Compare two sets

Question

Lets say, I have employees, and I know what fruits they like.

fruits(name, fruit_name)

My question is: list all the employees which at least like the same fruits as Donald.

So how do I compare two set of values?

This is how I get the fruits which Donald likes:

Select name, fruit_name
from fruits
where initcap(name) like '%Donald%';

Example: Donald likes apples, pears, peaches. I need the people who like apples, pears, peaches and possibly other fruits, but they must like those 3.

Answer 1

Two ways to do this:

Using Collections

I find this gives the most comprehensible SQL but it does require defining a collection type:

CREATE TYPE VARCHAR2s_Table AS TABLE OF VARCHAR2(20);

Then you can just group everything up into collections and use a self join and SUBMULTISET OF to find the other names.

WITH grouped AS (
  SELECT name,
         CAST( COLLECT( fruit ) AS VARCHAR2s_Table ) AS list_of_fruits
  FROM   fruits
  GROUP BY name
)
SELECT g.name
FROM   grouped f
       INNER JOIN
       grouped g
       ON (     f.list_of_fruits SUBMULTISET OF g.list_of_fruits
            AND f.name <> g.name )
WHERE  f.name = 'Alice';

SQLFIDDLE

Or an alternative version of this:

WITH grouped AS (
  SELECT name,
        CAST( COLLECT( fruit ) AS VARCHAR2s_Table ) AS list_of_fruits
  FROM fruits
  GROUP BY name
)
SELECT name
FROM   grouped
WHERE  name <> 'Alice'
AND    ( SELECT list_of_fruits FROM grouped WHERE name = 'Alice' )
       SUBMULTISET OF list_of_fruits ;

Not using Collections

WITH match_by_user AS (
  SELECT DISTINCT
         name,
         fruit
  FROM   fruits
  WHERE  name = 'Alice'
)
SELECT f.name
FROM   fruits f
       INNER JOIN
       match_by_user m
       ON (     f.fruit = m.fruit
            AND f.name  <> m.name )
GROUP BY f.name
HAVING COUNT( DISTINCT f.fruit ) = ( SELECT COUNT(1) FROM match_by_user );

SQLFIDDLE

As an aside - using INITCAP(Name) LIKE '%Name%' has the potential to match multiple names and you might find that you are finding the fruits that one of several people like.

Answer 2

You can use self join to get your desired result- I have tweaked your query a little to get the output-

select distinct e1.name from fruits e1,(Select name, fruit_name
from fruits
where initcap(name) like '%Donald%') e2
where e1.fruit_name = e2.fruit_name;

the above query returns employees for whom atleast one fruit matches with Donald

Below tested Query gives employees for whom atleast all the Donald's fruits matches

     select name from (
    select name,count(1) cnt  from
    (select name,fruit_name, case when fruit_name in (Select distinct fruit_name
        from fruits
        where initcap(name) like '%Donald%') then 1 else 0 end fruit_match from fruits)
    where fruit_match = 1 group by name) where  cnt >=
 (select count(distinct fruit_name) from fruits where initcap(name) like '%Donald%');