title: joining two select statements
body: <p>Can anyone tell me why the following won't work? It complains of a syntax error near the join key word between the two selects.</p>
  
  <pre><code>SELECT * 
  FROM ( select * from orders_products inner JOIN orders ON orders_products.orders_id = orders.orders_id  where products_id = 181) 
  as A
  
  join 
  
  SELECT * 
  FROM ( select * from orders_products INNER JOIN orders ON orders_products.orders_id = orders.orders_id  where products_id = 180) 
  as B
  
  on A.orders_id=B.orders_id
  </code></pre>
  
  <p>Basically my first <code>SELECT</code> pulls all the order info for a certain product from one table and pulls the quantity ordered from another and joins them together. The second <code>SELECT</code> does the same thing for another product. </p>
  
  <p>Now, I have</p>
  
  <pre>_______A_________         _______B_________<br>
  O_ID   P_ID   Q            O_ID   P_ID   Q <br>
  1       180   3            1       181   11<br>
  2       180   9            2       181   6<br>
  3       180   5            3       181   3<br></pre>
  
  <p>And, using another join I want to get </p>
  
  <pre><br>Q_ID   P_ID1   Q1    P_ID2    Q2
  <br>1       180    3      181     11
  <br>2       180    9      181      6
  <br>3       180    5      181      3</pre>
  
  <p>Maybe I am taking a wrong approach here. Any suggestions?</p>
  
  <p>UPDATE:
  Here is what worked for me after pointers by RedFilter:</p>
  
  <pre><code>(SELECT * 
  FROM (
  SELECT * FROM orders_products
  INNER JOIN orders ON orders_products.orders_id = orders.orders_id
  WHERE products_id =181) AS A
  LEFT JOIN (
  SELECT * FROM orders_products
  INNER JOIN orders ON orders_products.orders_id = orders.orders_id
  WHERE products_id =180) AS B ON A.orders_id = B.orders_id
  )
  UNION (
  SELECT * 
  FROM (
  SELECT * 
  FROM orders_products
  INNER JOIN orders ON orders_products.orders_id = orders.orders_id
  WHERE products_id =181
  ) AS C
  RIGHT JOIN (
  SELECT * 
  FROM orders_products
  INNER JOIN orders ON orders_products.orders_id = orders.orders_id
  WHERE products_id =180
  ) AS D ON C.orders_id = D.orders_id
  ) 
  </code></pre>