Left and right joining in a query

Question

A friend asked me for help on building a query that would show how many pieces of each model were sold on each day of the month, showing zeros when no pieces were sold for a particular model on a particular day, even if no items of any model are sold on that day. I came up with the query below, but it isn't working as expected. I'm only getting records for the models that have been sold, and I don't know why.

select days_of_months.`Date`,
       m.NAME as "Model",
       count(t.ID) as "Count"
  from MODEL m
  left join APPLIANCE_UNIT a on (m.ID = a.MODEL_FK and a.NUMBER_OF_UNITS > 0)
  left join NEW_TICKET t on (a.NEW_TICKET_FK = t.ID and t.TYPE = 'SALES'
and t.SALES_ORDER_FK is not null)
 right join (select date(concat(2009,'-',temp_months.id,'-',temp_days.id)) as "Date"
               from temp_months
              inner join temp_days on temp_days.id <= temp_months.last_day
              where temp_months.id = 3 -- March
             ) days_of_months on date(t.CREATION_DATE_TIME) =
date(days_of_months.`Date`)
 group by days_of_months.`Date`,
       m.ID, m.NAME

I had created the temporary tables temp_months and temp_days in order to get all the days for any month. I am using MySQL 5.1, but I am trying to make the query ANSI-compliant.

Answer 1

You should CROSS JOIN your dates and models so that you have exactly one record for each day-model pair no matter what, and then LEFT JOIN other tables:

SELECT  date, name, COUNT(t.id)
FROM    (
        SELECT ...
        ) AS days_of_months
CROSS JOIN
        model m
LEFT JOIN
        APPLIANCE_UNIT a
ON      a.MODEL_FK = m.id
        AND a.NUMBER_OF_UNITS > 0
LEFT JOIN
        NEW_TICKET t
ON      t.id = a.NEW_TICKET_FK
        AND t.TYPE = 'SALES'
        AND t.SALES_ORDER_FK IS NOT NULL
        AND t.CREATION_DATE_TIME >= days_of_months.`Date`
        AND t.CREATION_DATE_TIME < days_of_months.`Date` + INTERVAL 1 DAY
GROUP BY
        date, name

The way you do it now you get NULL's in model_id for the days you have no sales, and they are grouped together.

Note the JOIN condition:

AND t.CREATION_DATE_TIME >= days_of_months.`Date`
AND t.CREATION_DATE_TIME < days_of_months.`Date` + INTERVAL 1 DAY

instead of

DATE(t.CREATION_DATE_TIME) = DATE(days_of_months.`Date`)

This will help make your query sargable (optimized by indexes)

Answer 2

You need to use outer joins, as they do not require each record in the two joined tables to have a matching record.

http://dev.mysql.com/doc/refman/5.1/en/join.html

Answer 3

You're looking for an OUTER join. A left outer join creates a result set with a record from the left side of the join even if the right side does not have a record to be joined with. A right outer join does the same on the opposite direction, creates a record for the right side table even if the left side does not have a corresponding record. Any column projected from the table that does not have a record will have a NULL value in the join result.