Left join without using left join with 3 tables

Question

I have to get all data from 3 tables with left join. I did that with and without using left join. But my teacher ask another solution that uses just inner join. I just couldn't find the solution please give some advice. Here are the tables:

And the results should look like this:

With left join solution:

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

Without left join solution:

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

Answer 1

Here is one approach which uses only INNER JOIN and does not use LEFT JOIN anywhere. It begins with your original query using INNER JOIN instead of LEFT JOIN, and then adds to that result set the pieces which are missing from taking the inner product of all the tables.

SELECT t.* FROM
(
    SELECT O.ORDER_ID, O.ORDER_DATE, C.CUST_CODE, C.CUST_NAME, P.PART_CODE, P.PART_NAME 
    FROM ORDERS O
    INNER JOIN PART P
        ON P.PART_ID = O.PART_ID
    INNER JOIN CUSTOMER C
        ON C.CUST_ID = O.CUST_ID
    UNION
    SELECT O.ORDER_ID, O.ORDER_DATE, NULL AS CUST_CODE, NULL AS CUST_NAME, P.PART_CODE,
        P.PART_NAME 
    FROM ORDERS O
    INNER JOIN PART P
        ON P.PART_ID = O.PART_ID
    WHERE O.CUST_ID NOT IN (SELECT C.CUST_ID FROM CUSTOMER C)
        OR O.CUST_ID IS NULL
    UNION
    SELECT O.ORDER_ID, O.ORDER_DATE, C.CUST_CODE, C.CUST_NAME, NULL AS PART_CODE,
        NULL AS PART_NAME 
    FROM ORDERS O
    INNER JOIN CUSTOMER C
        ON C.CUST_ID = O.CUST_ID
    WHERE O.PART_ID NOT IN (SELECT P.PART_ID FROM PART P)
        OR O.PART_ID IS NULL
    UNION
    SELECT O.ORDER_ID, O.ORDER_DATE, NULL AS CUST_CODE, NULL AS CUST_NAME,
        NULL AS PART_CODE, NULL AS PART_NAME
    FROM ORDERS O
    WHERE (O.CUST_ID NOT IN (SELECT C.CUST_ID FROM CUSTOMER C) AND
           O.PART_ID NOT IN (SELECT P.PART_ID FROM PART P)) OR
           (O.CUST_ID IS NULL AND O.PART_ID IS NULL)
) t
ORDER BY t.ORDER_ID ASC

Follow the link below for a working demo:

SQLFiddle