mysql join query where multiple rows have same key value

Question

I have a table, in a third party database, that has two tables like these:

HISTORY
========
ID | ORDERED
1    PEAS
1    CARROTS
1    SPINACH
2    CARROTS
3    PEAS
3    CARROTS

PEOPLE
=====
ID | NAME
1    Jamal
2    Sharon
3    Mark

I am trying to create a MYSQL query that will return all the PEOPLE who ORDERED both PEAS and CARROTS. The results would be:

Jamal, Mark

When I try this with the OR operator, I get all three people:

SELECT a.ID from people a 
INNER JOIN history b on a.ID=b.ID 
WHERE b.ordered='PEAS' OR b.ordered='CARROTS'

When I try this with the AND operator, I get no people.

SELECT a.ID from people a 
INNER JOIN history b on a.ID=b.ID 
WHERE b.ordered='PEAS' AND b.ordered='CARROTS'

How can I write a query to get the names of the people who ordered peas and carrots given the table structure I have to work with?

Answer 1

JOIN twice, once for each condition:

SELECT a.ID
FROM people a 
JOIN history b on a.ID=b.ID AND b.ordered='PEAS'
JOIN history c on a.ID=c.ID AND c.ordered='CARROTS'

If history can contain duplicates, or to be defensive, add DISTINCT:

SELECT DISTINCT a.ID
FROM ...