'SQL Query for persons liking both fruits and vegetables [closed]

I have a situation I am trying to resolve. Might be simple but I can't figure it out. I have simplified my table that I am trying to pull data from. For this example I am using 2 columns, (persons and Fruits_vegetables). I am trying to filter only the person the like both fruits and vegetables. Please guide me.

Persons     Fruits_Vegetables
Michael     Apples
Michael     Oranges
Michael     Bell Peppers
Maria       Carrots
Maria       Mangoes
Maria       Bananas
Brenda      Bananas
Brenda      Mangoes
Brenda      Peaches
Alina       Mangoes
Alina       Grapes
Alina       Peaches
Nicole      Carrots
Nicole      Bell Peppers
Nicole      Lettuce
Christian   Carrots
Christian   Bananas
sqlfilterrows


Solution 1:[1]

Lots of ways:

select distinct t1.Persons
from T t1 inner join T t2 on t2.Persons = t1.Persons
where t1.Fruits_Vegetables in ('Apples', 'Oranges', 'Mangoes',
                               'Bananas', 'Peaches', 'Grapes')
  and t2.Fruits_Vegetables in ('Bell Peppers', 'Carrots', 'Lettuce')

I'm guessing this is a class exercise. Knowing that there's only a single person who can match across only two categories does make it easier to use a join approach. The problem is a bit contrived though it does apply concepts of joins, self-joins, many-to-many joins as well as using table aliases, in and distinct and possibly helping you to understand why combining the two values into a single column becomes problematic.

A more advanced technique might be:

with data as (
    select *,
        case when Fruits_Vegetables in (
            'Apples', 'Oranges', 'Mangoes', 'Bananas', 'Peaches', 'Grapes'
        ) then 'F'
             when Fruits_Vegetables in (
            'Bell Peppers', 'Carrots', 'Lettuce'
        ) then 'V' end as category
    from T
)
select Persons
from data
group by Persons
having count(distinct category) > 1

Solution 2:[2]

you need to have some idea about a master data list of all fruits and all vegetables so that the data in the column fruit_vegetables can be classified as fruit or vegetable.

Once you do that, you can use a query like below

select distinct Persons 
from yourtable t1
where 
exists 
(select 1 from yourtable t2  
where t1.Persons =t2.Persons
and t2.Fruits_Vegetables in ('Bell Peppers', 'Carrots')-- master list of veg
)
and 
(select 1 from yourtable t2  
where t1.Persons =t2.Persons
and t2.Fruits_Vegetables in ('Apples', 'Bananas')-- master list of fruits
)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 DhruvJoshi

Related Questions