Multiple self joins where joining condition not always exists

Question

I am trying to perform a multiple self join query where one of the conditions it is joining on does not always exist. I have a table that stores different sale events, where a single transaction (sale_id) can have multiple sale events associated with it - for example, transaction with sale_id 123 has four rows with sale_id 123 and sale_types SALE_PROPOSED, SALE_ACCEPTED, SALE_DONE, SALE_EXCEPTION.

I'm trying to write a query that joins all sale types based on sale_id and selects relevant information from them into a single result row. So far I got:

WITH SALES_TODAY AS(
SELECT *
FROM SALES
WHERE ..//date range logic for getting sales for the day)
SELECT SALE_PROPOSED.sale_id, SALE_PROPOSED.customer_id, SALE_ACCEPTED.price,
       SALE_ACCEPTED.accepted_date, SALE_DONE.done_date, SALE_EXCEPTION.exception_description
FROM SALES_TODAY SALE_PROPOSED, SALES_TODAY SALE_ACCEPTED, 
     SALES_TODAY SALE_DONE, SALES_TODAY SALE_EXCEPTION
WHERE (SALE_PROPOSED.sale_type='SALE_PROPOSED')
AND (SALE_PROPOSED.sale_id=SALE_ACCEPTED.sale_id AND SALE_ACCEPTED.sale_type='SALE_ACCEPTED')
AND (SALE_PROPOSED.sale_id=SALE_DONE.sale_id AND SALE_DONE.sale_type='SALE_DONE')
AND (SALE_EXCEPTION.SALE_TYPE='SALE_EXCEPTION');

Example table excerpt

sale_id |    sale_type    | ....// other columns
------------------------------------------
123     | SALE_PROPOSED
123     | SALE_ACCEPTED
123     | SALE_DONE
123     | SALE_EXCEPTION
456     | SALE_PROPOSED
456     | SALE_ACCEPTED
456     | SALE_DONE

While it works fine for sales that have all 4 events, it returns 0 rows for sales that don't have a sale_type 'SALE_EXCEPTION' type associated with them. I also have tried refactoring this SQL with left join for joining with 'SALE_EXCEPTION' but it returned duplicates for sales that had sale_type 'SALE_EXCEPTION' associated with them while working fine for sales without such event. Could anyone advice what my query is missing so that it would return correct results despite whether a sales_id has sale_type 'SALE_EXCEPTION' or not. Thank you.

Answer 1

You use INNER JOIN (in comma-delimited tablenames form) whereas you must use LEFT JOIN.

WITH SALES_TODAY AS( /* daily selection logic */ ),

     sale_id_list AS ( SELECT DISTINCT sale_id FROM SALES_TODAY )

SELECT sale_id_list.sale_id, ...
FROM sale_id_list 
LEFT JOIN SALES_TODAY AS SALE_PROPOSED 
    ON SALE_PROPOSED.sale_id=sale_id_list.sale_id
    AND SALE_PROPOSED.sale_type='SALE_PROPOSED'
LEFT JOIN SALES_TODAY AS SALE_ACCEPTED
    ON SALE_ACCEPTED.sale_id=sale_id_list.sale_id
    AND SALE_ACCEPTED.sale_type='SALE_ACCEPTED'
LEFT JOIN ...