Suppose I have these tables:
person
id name
-- ----
1 dude
2 john
3 doe
...etc
favourite_food
personid food
-------- ------
1 apples
5 apples
5 oranges
And I want to get a list of the names of people who like at least the foods that person 5 likes. Something like below:
SELECT p.name FROM person p
LEFT JOIN favourite_food ff ON ff.personid = p.id
WHERE ff.food = (SELECT food FROM favourite_food WHERE personid = 5)
AND ff.personid <> 5;
Except I have no idea how to specify the 'at least' part. Do I have to create a temporary table or so?
SQL essentially works with sets, so it often helps to reformulate your problem strictly in terms of set theory.
"At least" could be reformulated this way: If we look only at foods that are favourite foods of person 5, which persons have the same number of favourite foods as person 5?
SELECT name
FROM person
WHERE id IN (SELECT personid
FROM favourite_food
WHERE food IN (SELECT food
FROM favourite_food
WHERE personid = 5)
GROUP BY personid
HAVING COUNT(food) = (SELECT COUNT(food)
FROM favourite_food
WHERE personid = 5)
)
Alternatively, use this reformulation: We do not want persons who do not like a food that person 5 likes. Therefore, find all persons for which no food exists that is liked by person 5 but not liked by that person:
SELECT name
FROM person
WHERE NOT EXISTS (SELECT 1
FROM favourite_food AS person5_food
WHERE personid = 5
AND NOT EXISTS (SELECT 1
FROM favourite_food
WHERE personid = person.id
AND food = person5_food.food)
)
(Actually, SQL is based on the relational algebra, and the operation you want is called division.)