Hello,
I have a table (parcelplanning) with deliveries.
Each delivery has a unique "id", a "zipcode", and the requested delivery "hour".
I also have a table (distri_blocks) that lists for each possible "zipcode" the "area" that the zipcode belongs to (so a 1 to many relationship for the area)
I also have a table (distri_distance) that lists the distance between 2 areas.
To make let a customer plan his delivery (to his own house), for each day and each delivery-period I have to know the sum of deliveries to the areas with the same distance to his own area.
Parcelplanning
1. Monday 8:00 zip=A1
2. Monday 8:00 zip=B1
3. Monday 9:00 zip=A1
4. Monday 8:00 zip=A3
5. Monday 8:00 zip=A2
Distri_blocks
zip=A1 => block=1
zip=B1 => block=1
zip=A2 => block=2
zip=A3 => block=3
zip=A4 => block=4
Distri_distance blocks
1 > 1 = 0
1 > 2 = 1
1 > 3 = 1
1 > 4 = 2
2 > 1 = 1
2 > 2 = 0
2 > 3 = 2
2 > 4 = 1
Result for somebody living in zip=A2 should be:
Mon 8:00 dist=0 => count=1 (parcel no.5)
Mon 8:00 dist=1 => count=2 (parcel no.1,2)
Mon 8:00 dist=2 => count=1 (parcel no.4)
Mon 9:00 dist=1 => count=1 (parcel. no.3)
Below is the query I use. But it doesn't produce any results...guess my multiple left join is incorrect. I am not using any indexes on the tables (as far as I know...). I am running a mysql database. (I had it working when I was only using 2 tables, but I do need more tables now...)
$q1 = mysql_query ("
SELECT DD.distance AS distance, PP.hour AS period, PP.pref_date AS date, count(PP.id) AS quantity
FROM parcelplanning PP
LEFT JOIN distri_blocks DB ON PP.zipcode = DB.zipcode
LEFT JOIN distri_distance DD ON DB.area = DD.area_B
WHERE DD.area_A={$area} AND date>'{$today}'
GROUP BY date, period, distance");
