MYSQL table calendar and right Join

Question

How can I fill gaps between rows? Im using table calendar but my query does not seem to be correct. The result is not showing the gaps..

SELECT calendar.datefield, t2.*
    FROM store_relation t1,store t3, store_product t2
            RIGHT JOIN calendar ON (DATE(t2.created) = calendar.datefield)
            WHERE (calendar.datefield BETWEEN ('2012-07-15') and ('2012-07-20'))
    AND t1.id_store_a = 1
    AND t1.id_store_b = t2.id_store
    AND t3.id = t2.id_store
    AND t2.id_product = 11
    ORDER By t2.price

Result:

datefield   |   id_product   |   id_store   |   created
2012-07-15      1                1              2012-07-15
2012-07-18      1                1              2012-07-18
2012-07-20      1                1              2012-07-20
2012-07-20      1                1              2012-07-20

I was waiting for the result

datefield   |   id_product   |   id_store   |   created
2012-07-15      1                1              2012-07-15
2012-07-16      null             null           null
2012-07-17      null             null           null
2012-07-18      1                1              2012-07-18
2012-07-19      null             null           null
2012-07-20      1                1              2012-07-20
2012-07-20      1                1              2012-07-20

@MohamedNuur `RIGHT JOIN` and `RIGHT OUTER JOIN` are the same thing — Taryn, Jul 26 '12 at 18:14
generating the missing numbers in a sequence is a common problem. You can see my usual solution here: http://stackoverflow.com/questions/11020667/how-to-get-all-dates-in-a-month/11021035#11021035 — dnagirl, Jul 26 '12 at 18:16
The answers below are great. You should be writing your SQL fully using proper JOIN syntax, which means using the ON clause instead of WHERE. — Gordon Linoff, Jul 26 '12 at 18:27

score 2 · Accepted Answer · answered Jul 26 '12 at 18:17

When you have a LEFT/RIGHT JOIN and you are filtering on tables that are optionally included, the WHERE clause will leave those records out. You need to include a condition that allows for the left part of the RIGHT JOIN being missing:

WHERE (calendar.datefield BETWEEN ('2012-07-15') and ('2012-07-20'))
AND
(
   (t1.id_store_a = 1
   AND t1.id_store_b = t2.id_store
   AND t3.id = t2.id_store
   AND t2.id_product = 11)
   OR
   (t2.id_product is NULL)
)

For example, if you look at your expected output, the value of id_product is null, but you are filtering for t2.id_product = 11. So the null records are never going to match 11, and that's why they are being left out.

score 1 · Answer 2 · answered Jul 26 '12 at 18:17

You are referencing some of the columns on the left side in your WHERE clause. Try explicitly adding an OR NULL condition. For example:

SELECT calendar.datefield, t2.*
FROM store_relation t1,store t3, store_product t2
        RIGHT JOIN calendar ON (DATE(t2.created) = calendar.datefield)
        WHERE (calendar.datefield BETWEEN ('2012-07-15') and ('2012-07-20'))
AND ((t1.id_store_a = 1
        AND t1.id_store_b = t2.id_store
        AND t3.id = t2.id_store
        AND t2.id_product = 11)
    OR (t1.id IS NULL AND t2.id IS NULL AND t3.id IS NULL))
ORDER By t2.price

You'll have to decide which table(s) you want to allow to be null.

score 0 · Answer 3 · answered Jul 26 '12 at 18:51

Ok I got it...Thank you for giving me new ideas

SELECT calendar.datefield as DATE, t2.*
FROM store_relation t1,store t3, store_product t2
    RIGHT JOIN calendar ON (DATE(t2.created) = calendar.datefield)
    WHERE (calendar.datefield BETWEEN ('2012-07-15') and ('2012-07-27'))
AND ((t1.id_store_a = 1
    AND t1.id_store_b = t2.id_store
    AND t3.id = t2.id_store
    AND t2.id_product = 11)
OR (t2.id_product IS NULL))
GROUP BY DATE

MYSQL table calendar and right Join

3 Answers3