我必须使用 left join
从3个表中获取所有数据。我已经尝试使用和不使用 left join
来实现。但是我的老师要求使用只有 inner join
的另一种解决方案。我无法找到这个解决方案,请给予一些建议。以下是这些表:
结果应该如下所示:
使用左连接的解决方案:SELECT O.*,C.CUST_CODE,C.CUST_NAME,P.PART_CODE,P.PART_NAME
FROM ORDERS O
LEFT OUTER JOIN PART P ON P.PART_ID = O.PART_ID
LEFT OUTER JOIN CUSTOMER C ON C.CUST_ID = O.CUST_ID
没有 left join
解决方案:
SELECT O.*,
(SELECT C.CUST_CODE FROM CUSTOMER C WHERE C.CUST_ID=O.CUST_ID) AS CUST_CODE,
(SELECT C.CUST_NAME FROM CUSTOMER C WHERE C.CUST_ID=O.CUST_ID) AS CUST_NAME,
(SELECT P.PART_CODE FROM PART P WHERE P.PART_ID = O.PART_ID ) AS PART_CODE,
(SELECT P.PART_NAME FROM PART P WHERE P.PART_ID = O.PART_ID ) AS PART_NAME
FROM ORDERS O