title: Intersection in mysql for multiple values
body: <p>The <code>intersect</code> keyword is not available in mysql. I want to know how to implement the following in mysql db.  My tables are:</p>
  
  <pre><code>customer(cid,city,name,state)
  orders(cid,oid,date)
  product(pid,price,productname)
  lineitem(lid,pid,oid,totalquantity,totalprice)
  </code></pre>
  
  <p>I want the products bought by all the customers of a particular city 'X'. i.e. every customer in city 'x' should have bought the product. I managed to select the oid's and the pid's of customers living in that particular city. Now I should select the pid's which is present in all the oid's.</p>
  
  <p>Example.</p>
  
  <pre><code>Oid     Pid
  2400     1
  2400     2
  2401     3
  2401     1
  2402     1
  2403     1
  2403     3
  </code></pre>
  
  <p>The answer from the above input should be 1 because it is present in all oid's. The query which I used to get the oid's and pid's:</p>
  
  <pre><code>select t.oid,l.pid
    from lineitem l
    join (select o.oid,c1.cid
            from orders o
            join (select c.cid
                    from customer c
                    where c.city='X') c1
            where o.cid=c1.cid) t on l.oid=t.oid 
  </code></pre>
  
  <p>Now I need to intersect all the oid's and get the result.The query should not be dependent on data.</p>