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
)

Could you describe what you would like to retrieve from this query? There may be a more efficient way. — John Kane, May 17 '11 at 19:19

score 84 · Accepted Answer · edited Jun 05 '18 at 08:25

84

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

edited Jun 05 '18 at 08:25

Rishikesh Chandra

661
9
25

answered May 17 '11 at 19:15

D'Arcy Rittich

167,292
40
290
283

You should double check your answer, it is exactly what the OP has posted in his question. – maple_shaft May 17 '11 at 19:18
4

@maple_shaft it may look that way, but I removed `SELECT * FROM` after the `JOIN` clause. – D'Arcy Rittich May 17 '11 at 19:20
1

and that did the trick. sorry my question was unclear at first, but while i was adding more details you guys posted the answers and this one turned out to do what i want. – Codrguy May 17 '11 at 19:38
you know actually, i think i need a Full Join for my objective and if i substitue 'Full Join' instead of 'join' in your original solution, i get an error. do you have any ideas on how to solve it. Basically i need to see all my orders in the results set even if an order only had one of the products in it. – Codrguy May 17 '11 at 19:58
@Codrguy That will do what you want, but mysql does not support `FULL OUTER JOIN`, but here is a workaround: http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/ – D'Arcy Rittich May 17 '11 at 20:02

score 12 · Answer 2 · edited Jun 05 '18 at 07:56

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)

score 3 · Answer 3 · edited May 17 '11 at 19:20

3

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);

edited May 17 '11 at 19:20

onedaywhen

55,269
12
100
138

answered May 17 '11 at 19:16

maple_shaft

10,435
6
46
74

score -1 · Answer 4 · edited Apr 12 '18 at 12:13

-1

SELECT *
FROM
  (First_query) AS ONE
LEFT OUTER JOIN
  (Second_query ) AS TWO ON ONE.First_query_ID = TWO.Second_Query_ID;

edited Apr 12 '18 at 12:13

Gregor Doroschenko

11,488
5
25
37

answered Apr 12 '18 at 11:00

Narendra Kumar Achari

5
2

Please add an exploration to your answer – Gregor Doroschenko Apr 12 '18 at 12:12
SELECT * FROM (SELECT c.name AS COUNTRY_NAME FROM STATS_TABLE ebs INNER JOIN CLASS_TABLE ebsec ON ebs.ID = ebsec.STATS_TABLE_id AND ebs.USER_ID = ebsec.user_id INNER JOIN classes ec ON ebsec.class_id = ec.id WHERE ebs.created_at blabla GROUP BY es.id) AS **ea** **LEFT OUTER JOIN** (SELECT es.School_id FROM STATS_TABLE ebs INNER JOIN CLASS_TABLE ebsec ON ebs.ID = ebsec.STATS_TABLE_id AND USER_ID = ebsec.user_id WHERE ebs.`STATUS` IN ('COMPLETED') AND ebs.creted GROUP BY es.id) AS **ear** ON ea.School_id = ear.School_id; – Narendra Kumar Achari Jul 09 '18 at 13:07

joining two select statements

4 Answers4

Linked