title: SQL filtering by multiple items in the same column
body: <p>I've got two tables in SQL, one with a project and one with categories that projects belong to, i.e. the JOIN would look roughly like:</p>
  
  <pre><code>Project | Category
  --------+---------
    Foo   | Apple
    Foo   | Banana
    Foo   | Carrot
    Bar   | Apple
    Bar   | Carrot
    Qux   | Apple
    Qux   | Banana
  </code></pre>
  
  <p>(Strings replaced with IDs from a higher normal form, obviously, but you get the point here.)</p>
  
  <p>What I want to do is allow filtering such that users can select any number of categories and results will be filtered to items that are members of all the selected categories. For example, if a user selects categories "Apple" and "Banana", projects "Foo" and "Qux" show up. If a user select categories "Apple", "Banana", and "Carrot" then only the "Foo" project shows up.</p>
  
  <p>The first thing I tried was a simple SELECT DISTINCT Project FROM ... WHERE Category = 'Apple' AND Category = 'Banana', but of course that doesn't work since Apple and Banana show up in the same column in two different rows for any common project.</p>
  
  <p>GROUP BY and HAVING don't do me any good, so tell me: is there an obvious way to do this that I'm missing, or is it really so complicated that I'm going to have to resort to recursive joins?</p>
  
  <p>This is in PostgreSQL, by the way, but of course standard SQL code is always preferable when possible.</p>