title: multiple WHERE clauses on a joined table
body: <p>I have a simple application that tracks diners and their favorite flavors and desserts. The <code>records</code> table is just the diner's name and ID, the <code>mid</code> table tracks the desserts and flavors (again by an ID linked to another table of values).</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `records` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
  
  
  INSERT INTO `records` (`id`, `name`) VALUES
  (1, 'Jimmy Jones'),
  (2, 'William Henry');
  
  
  CREATE TABLE IF NOT EXISTS `mid` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `diner` int(11) NOT NULL,
    `dessert` int(11) NOT NULL DEFAULT '0',
    `flavor` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
  
  
  INSERT INTO `mid` (`id`, `diner`, `dessert`, `flavor`) VALUES
  (1, 1, 3, 0),
  (2, 1, 2, 0),
  (3, 1, 15, 0),
  (4, 1, 0, 1),
  (5, 2, 3, 0),
  (6, 2, 6, 0),
  (7, 2, 0, 4),
  (8, 1, 34, 0),
  (9, 2, 0, 4),
  (10, 2, 0, 22);
  </code></pre>
  
  <p>I'm a little stumped by what should be a simple query-- I want to get all IDs from the <code>records</code> table where certain dessert or flavor requirements are met:</p>
  
  <pre><code>SELECT a.id 
  FROM records AS a
  JOIN mid AS b ON a.id = b.diner
  WHERE b.dessert IN (3,2,6)
  AND b.flavor IN (4,22)
  </code></pre>
  
  <p>This query returns no rows, even though there are records that match the where clauses. I am pretty sure I'm missing something obvious with the <code>JOIN</code> but I've tried INNER, OUTER, LEFT and RIGHT with no success.</p>
  
  <p>Can someone put me on the right track and explain what I'm missing?</p>
  
  <p>Thanks</p>