Let's say I have a table with 10 people and each person has a different number of different fruits. I want to input the exact combination of fruits and have SQL spit out the person (if they exist) who has that exact combination of fruits.

For example:

Select Person, Fruit, Number of Fruits

Where Fruit1 = Orange and Number of Fruits = 6
and Fruit2 = Banana and Number of Fruits = 3
and Fruit3 = Apple and Number of Fruits = 1

I want this to return Adam and his Fruits and their quantities. How can I do that in SQL? Thanks in advance.

CodePudding user response：

Here is a possible solution:

create table people(
name varchar(10),
fruit varchar(10),
qty int);
insert into people values
('Mary','Apple',  5),
('Mary','Banana', 4),
('Mary','Orange', 1),
('John','Banana', 3),
('John','Kiwi',           7),
('Adam','Orange', 6),
('Adam','Banana', 3),
('Adam','Apple',  1);

✓

✓

select * from people;

name | fruit  | qty
:--- | :----- | --:
Mary | Apple  |   5
Mary | Banana |   4
Mary | Orange |   1
John | Banana |   3
John | Kiwi   |   7
Adam | Orange |   6
Adam | Banana |   3
Adam | Apple  |   1

select name from people
where (fruit='Orange' and qty=6)
or (fruit='Banana' and qty=3)
or (fruit='Apple' and qty=1)
group by name
having count(fruit)=3;

| name |
| :--- |
| Adam |

db<>fiddle here

CodePudding user response：

SELECT Person FROM
(
SELECT
 Person, 
 SUM(CASE WHEN Fruit = 'Orange' THEN Number_of_fruits ELSE 0 END) as cnt_orange,
 SUM(CASE WHEN Fruit = 'Apple' THEN Number_of_fruits ELSE 0 END) as cnt_apple,
 SUM(CASE WHEN Fruit = 'Banana' THEN Number_of_fruits ELSE 0 END) as cnt_banana,
 SUM(CASE WHEN Fruit = 'Kiwi' THEN Number_of_fruits ELSE 0 END) as cnt_kiwi,
 ...
 sum aggregation for other fruits
 ...
FROM yourtable
GROUP BY Person
) t
WHERE cnt_orange = 6 AND cnt_banana = 3 AND cnt_apple = 1
  

CodePudding user response：

You're essentially dealing with an Entity-Attribute-Value data model, where the person is the entity, the fruit is the attribute, and the number of fruits is the value.

To solve this, you'd want to use the normal EAV pattern of a series of self-joins., one for each attribute (fruit) you want to return.

SELECT t1.person, t1.fruit, t1.num_fruits, t2.fruit, t2.num_fruits, t3.fruit, t3.num_fruits
    FROM YourTable t1
        INNER JOIN YourTable t2
            ON t1.person = t2.person
                AND t2.fruit = 'banana' 
                AND t2.num_fruits = 3
        INNER JOIN YourTable t3
            ON t1.person = t3.person
                AND t3.fruit = 'apple' 
                AND t3.num_fruits = 1
    WHERE t1.fruit = 'orange' 
        AND t1.num_fruits = 6;