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.
PIVOT
the data instead?