joining two select statements

Question

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   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   Basically my first SELECT 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 SELECT does the same thing for another product    Now  I have         A                         B          O ID   P ID   Q            O ID   P ID   Q  1       180   3            1       181   11 2       180   9            2       181   6 3       180   5            3       181   3  And  using another join I want to get   Q ID   P ID1   Q1    P ID2    Q2 1       180    3      181     11 2       180    9      181      6 3       180    5      181      3  Maybe I am taking a wrong approach here  Any suggestions   UPDATE  Here is what worked for me after pointers by RedFilter    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

User · Answer

SELECT   FROM    First query  AS ONE LEFT OUTER JOIN    Second query   AS TWO ON ONE First query ID   TWO Second Query ID

User · Answer

Not sure what you are trying to do  but you have two select clauses  Do this instead   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 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   Update   You could probably reduce it to something like this   SELECT o orders id          op1 products id          op1 quantity          op2 products id          op2 quantity FROM orders o INNER JOIN orders products op1 on o orders id   op1 orders id   INNER JOIN orders products op2 on o orders id   op2 orders id   WHERE op1 products id   180 AND op2 products id   181

User · Answer

This will do what you want   select      from orders products         INNER JOIN orders            ON orders products orders id   orders orders id  where products id in  180  181

User · Answer

You should use UNION if you want to combine different resultsets  Try the following    SELECT     FROM   SELECT            FROM orders products          INNER JOIN orders ON orders products orders id   orders orders id           WHERE products id   181  AS A  UNION    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

[mysql] joining two select statements

Examples related to mysql

Examples related to sql

Examples related to join