0

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.

2
  • Have you tried to PIVOT the data instead? – Michael Kutz Oct 7 '20 at 16:53
  • You use INNER JOIN (in comma-delimited tablenames form) whereas you must use LEFT JOIN. – Akina Oct 7 '20 at 17:40
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 ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.