두 선택 문 결합

두 선택 문 결합

누가 왜 아래와 같은 것들이 작동하지 않는지 말해줄 수 있습니까?선택한 두 개 사이의 결합키워드 근처에 구문 오류가 있다고 호소합니다.

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_________________________________________________________________

O_IDP_IDQO_IDP_IDQ

1       180   3            1       181   11

2       180   9            2       181   6

3       180   5            3       181   3

그리고 또 다른 조인을 사용하는 것은

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

무엇을 하려고 하는지 확실하지 않지만 두 개의 선택 항이 있습니다.대신 이렇게 하십시오.

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

업데이트:

이를 다음과 같이 줄일 수 있습니다.

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

서로 다른 결과 집합을 결합하려면 UNION을 사용해야 합니다.다음을 시도해 봅니다.

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

이렇게 하면 원하는 작업을 수행할 수 있습니다.

select * 
  from orders_products 
       INNER JOIN orders 
          ON orders_products.orders_id = orders.orders_id
 where products_id in (180, 181);

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

언급URL : https://stackoverflow.com/questions/6035901/joining-two-select-statements