Complex MySQL query between two tables

Question

Hi,

I've a real complex query here, at least for me.

Here's a table with car dates releases (where model_key = 320D):

+------------+-----------+
| date_key   | model_key |
+------------+-----------+
| 2003-08-13 | 320D      | 
| 2005-11-12 | 320D      |
| 2007-02-11 | 320D      | 
+------------+----------+

Then I have a table with daily purchases (where model_key = 320D):

+------------+-----------+-----------+
| date_key   | model_key | sal_quant |
+------------+-----------+ ----------+
| 2003-08-13 | 320D      | 0         |
| 2003-08-14 | 320D      | 1         |
| 2003-08-15 | 320D      | 2         |
| 2003-08-16 | 320D      | 0         |
...
| 2005-11-12 | 320D      | 2         |
| 2005-11-13 | 320D      | 0         |
| 2005-11-14 | 320D      | 4         |
| 2005-11-15 | 320D      | 3         |
... 
| 2007-02-11 | 320D      | 1         |
| 2007-02-12 | 320D      | 0         |
| 2007-02-13 | 320D      | 0         |
| 2007-02-14 | 320D      | 0         |
... 
+------------+-----------+-----------|

I want to know the sum of car sales by day after each release during N days.

I want a table like (assuming 4 days analysis):

+-----------------+
| sum(sal_quant)  |
+-----------------+
| 3               | 
| 1               |
| 6               |
| 3               |
+-----------------+

Which means, in the first day of car release, 3 BMW 320D were sold, in the second day, just one,... an so on.

Now I have the following query:

SELECT SUM(sal_quant) 
FROM daily_sales 
WHERE model_key='320D' 
AND date_key IN (
    SELECT date_key FROM car_release_dates WHERE model_key='320D')

But this only gives me the sum for the first release day. How can I get the next days?

Thanks.

Answer 1

I suppose there's no way a car could be sold before its release date. So you could simply group on the date_key:

SELECT date_key, SUM(sal_quant) 
FROM daily_sales 
WHERE model_key='320D' 
GROUP BY date_key
ORDER BY date_key DESC
LIMIT 4

If it is possible to sell cars before the release date, add a WHERE clause:

AND date_key >= (
   SELECT date_key FROM car_release_dates WHERE model_key='320D')

EDIT: Per your comment, you can constrain the query to within four days of multiple release dates with an INNER JOIN. For example:

SELECT ds.date_key, SUM(ds.sal_quant) 
FROM daily_sales ds
INNER JOIN car_release_dates crd
    ON crd.model_key = ds.model_key
    AND ds.date_key BETWEEN crd.date_key
        AND DATE_ADD(crd.date_key, INTERVAL 4 DAY)
WHERE ds.model_key='320D' 
GROUP BY ds.date_key
ORDER BY ds.date_key DESC