有人能告诉我为什么以下代码无法正常工作吗?它会抱怨在两个select之间的join关键字附近有一个语法错误。
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
基本上,我的第一个SELECT
从一张表中获取某个产品的所有订单信息,并从另一张表中获取订购数量,然后将它们连接起来。第二个SELECT
对另一个产品执行相同的操作。
现在,我有了
_______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
并且,使用另一个连接,我想要得到
Q_ID P_ID1 Q1 P_ID2 Q2
1 180 3 181 11
2 180 9 181 6
3 180 5 181 3
也许我在这里做错了什么。有什么建议吗?
更新: 在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
)