Multiple self joins where joining condition not always exists

join;oracleself-join

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.

Best Answer

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 ...

Related Solutions

Mysql – How to optimize multiple self-JOINs

I don't think there is a need for denormalizing the table. Self-joining will work fine if you have indexes that will be used effectively. For the specific query, I'd add an index on (year, dataID, countryID, data) and not use any derived tables:

SELECT 
    c.countryName AS Country,   
    em01.data AS data01,
    em02.data AS data02,
    ...
    emXX.data AS dataXX
FROM  
    Countries AS c
      JOIN CountryData AS em01
        ON  em01.year = 2017
        AND em01.dataID = 523 
        AND em01.countryID = c.countryID 
      JOIN CountryData AS em02
        ON  em02.year = 2017
        AND em02.dataID = 524
        AND em02.countryID = c.countryID
      ... 
      JOIN CountryData AS emXX
        ON  emXX.year = 2017
        AND emXX.dataID = YYY
        AND emXX.countryID = c.countryID 
 ;

The only issue you'll have is that MySQL has a hard limit of maximum 61 joins in a query. So, you won't be able to have 90 columns with the above query.

Another observation is that you don't seem to need any aggregated data but just a tiny subset of the (small or big table, doesn't matter). With the suggested index, you could just write a query like this:

SELECT 
    cd.countryID,
    c.countryName AS Country,   
    cd.dataID,
    cd.data
FROM  
    Countries AS c
      JOIN CountryData AS cd
        ON  cd.countryID = c.countryID 
WHERE 
      cd.year = 2017
  AND cd.dataID IN (522, 523, ..., YYY) 
ORDER BY
    cd.countryID,
    cd.dataID ;

and have the pivot transformation done in your application.

How toNNER JOIN and OUTER JOIN within the same table

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors.

Something like this will work:

SELECT 
    m.month, 
    p.name, 
    COALESCE(r.receipts_quantity, 0) AS receipts_quantity, 
    COALESCE(s.sales_quantity, 0)    AS sales_quantity
FROM 
    product AS p
  CROSS JOIN
    ( VALUES (1),(2),(3),(4),(5),(6) ) AS m(month)     -- months wanted in the report
  LEFT OUTER JOIN 
    ( SELECT month, product_id, SUM(quantity) AS receipts_quantity
      FROM receipts
      GROUP BY month, product_id
    ) AS r
    ON  p.id_product = r.product_id
    AND m.month = r.month
  LEFT OUTER JOIN  
    ( SELECT month, product_id, SUM(quantity) AS sales_quantity
      FROM sales
      GROUP BY month, product_id
    ) AS s
    ON  p.id_product = s.product_id
    AND m.month = s.month ;

If you want to avoid having results for products that did not have any activity in a month, you can add the condition:

WHERE r.month IS NOT NULL 
   OR s.month IS NOT NULL

Best Answer

Related Solutions

Mysql – How to optimize multiple self-JOINs

How toNNER JOIN and OUTER JOIN within the same table

Related Question