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