title: MongoDB/CouchDB: Join a table to itself
body: <p>I have a relational data model I'm thinking about migrating to either MongoDB or CouchDB, and I'm trying to understand how the queries would work. Suppose I have two entities, Employees and Projects, and a many-to-many join table called Assignments. I want to query for all the projects on which two users collaborated. In SQL I could do something like this:</p>
  
  <pre><code>SELECT DISTINCT a1.project_id
  FROM assignments a1, assignments a2
  WHERE a1.project_id = a2.project_id
  AND a1.employee_id = ?
  AND a2.employee_id = ?
  </code></pre>
  
  <p>How would I do this in NoSQL, assuming I have Employee, Project, and Assignment "documents"? Or would you structure the documents differently, and how would that affect the query?</p>
  
  <p>I'd be happy to hear answers both for Mongo's query API and Couch's map/reduce approach.</p>