Stuck on a query

[kevinfwb]

Here is the query that I'm using now

 

SELECT p1.storeNum, p1.year, p1.period, p1.sales AS week1, p2.sales AS week2, p3.sales AS week3, p4.sales AS week4

FROM Sales AS p1, Sales AS p2, Sales AS p3, Sales AS p4

WHERE

p1.storeNum = p2.storeNum AND p2.storeNum = p3.storeNum AND p3.storeNum = p4.storeNum AND

p1.week = 1 AND p2.week=2 AND p3.week=3 AND p4.week=4 AND

p1.year=2007 AND p2.year=2007 AND p3.year=2007 AND p4.year=2007

AND p1.period=6 AND p2.period=6 AND p3.period=6 AND p4.period=6;

 

Here is the problem.  Our sales are divided into 4 weeks in a period.  If the period is not complete yet and only data is entered for weeks 1 and 2 the query returns 0 rows, assumingly because weeks 3 and 4 are blank which makes the entire query null.

 

How can I rewrite the query to still give me results for the weeks that have been completed and omit the ones that haven't?

 

Thanks for your help

 

-Kevin

[BenInBlack]

when you do it with

p1.storeNum = p2.storeNum AND p2.storeNum = p3.storeNum AND p3.storeNum = p4.storeNum

you are in essence creating inner joins

 

you better by adding the store table as the driving table and use joins like this

 

SELECT s1.storeNum, p1.year, p1.period, p1.sales AS week1, p2.sales AS week2, p3.sales AS week3, p4.sales AS week4
FROM Stores S1
LEFT OUTER JOIN Sales p1 on S1.storeNum = p1.storeNum
LEFT OUTER JOIN Sales p2 on S1.storeNum = p2.storeNum
LEFT OUTER JOIN Sales p3 on S1.storeNum = p3.storeNum
LEFT OUTER JOIN Sales p4 on S1.storeNum = p4.storeNum
WHERE
p1.week = 1 AND p2.week=2 AND p3.week=3 AND p4.week=4 AND
p1.year=2007 AND p2.year=2007 AND p3.year=2007 AND p4.year=2007
AND p1.period=6 AND p2.period=6 AND p3.period=6 AND p4.period=6;

 

This way you will get date for incomplete periods

 

disclaimer: not tested sql statement, your mileage may vary

[kevinfwb]

I appreciate the response.  The query ran for about two minutes before I stopped it.  This is a relatively large table (30,000 entries).  EXPLAIN shows the following:

 

+----+-------------+-------+------+---------------------------+------------------+---------+-------------------+------+------------------------------------------------+

| id | select_type | table | type | possible_keys            | key              | key_len | ref              | rows | Extra                                          |

+----+-------------+-------+------+---------------------------+------------------+---------+-------------------+------+------------------------------------------------+

|  1 | SIMPLE      | p2    | ref  | year_week_period,storeNum | year_week_period |      12 | const,const,const |  134 | Using where                                    |

|  1 | SIMPLE      | p3    | ref  | year_week_period,storeNum | year_week_period |      12 | const,const,const |  134 | Using where                                    |

|  1 | SIMPLE      | p4    | ref  | year_week_period,storeNum | year_week_period |      12 | const,const,const |  134 | Using where                                    |

|  1 | SIMPLE      | p1    | ref  | year_week_period,storeNum | year_week_period |      12 | const,const,const |  135 | Using where                                    |

|  1 | SIMPLE      | S1    | ALL  | PRIMARY,rfc_storeNum      | NULL            |    NULL | NULL              |  156 | Range checked for each record (index map: 0x5) |

+----+-------------+-------+------+---------------------------+------------------+---------+-------------------+------+------------------------------------------------+

5 rows in set (0.00 sec)

 

Thanks

 

-Kevin

 

[BenInBlack]

You can try this, this might use your indexes better.

 

SELECT s1.storeNum, p1.year, p1.period, p1.sales AS week1, p2.sales AS week2, p3.sales AS week3, p4.sales AS week4
FROM Stores S1
LEFT OUTER JOIN Sales p1 on S1.storeNum = p1.storeNum AND p1.year=2007 AND p1.week = 1 AND p1.period=6
LEFT OUTER JOIN Sales p2 on S1.storeNum = p2.storeNum AND p2.year=2007 AND p2.week=2 AND p2.period=6 
LEFT OUTER JOIN Sales p3 on S1.storeNum = p3.storeNum AND p3.year=2007 AND p3.week=3 AND p3.period=6 
LEFT OUTER JOIN Sales p4 on S1.storeNum = p4.storeNum AND p4.year=2007 AND p4.week=4 AND p4.period=6