title: mysql join query where multiple rows have same key value
body: <p>I have a table, in a third party database, that has two tables like these:</p>
  
  <pre><code>HISTORY
  ========
  ID | ORDERED
  1    PEAS
  1    CARROTS
  1    SPINACH
  2    CARROTS
  3    PEAS
  3    CARROTS
  
  PEOPLE
  =====
  ID | NAME
  1    Jamal
  2    Sharon
  3    Mark
  </code></pre>
  
  <p>I am trying to create a MYSQL query that will return all the PEOPLE who ORDERED both PEAS and CARROTS. The results would be: </p>
  
  <p>Jamal, Mark</p>
  
  <p>When I try this with the OR operator, I get all three people:</p>
  
  <pre><code>SELECT a.ID from people a 
  INNER JOIN history b on a.ID=b.ID 
  WHERE b.ordered='PEAS' OR b.ordered='CARROTS'
  </code></pre>
  
  <p>When I try this with the AND operator, I get no people.</p>
  
  <pre><code>SELECT a.ID from people a 
  INNER JOIN history b on a.ID=b.ID 
  WHERE b.ordered='PEAS' AND b.ordered='CARROTS'
  </code></pre>
  
  <p>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?</p>