Help with Multiple JOIN Query

[UltraSmooth]

I have three tables that I need to left join and I'm having some problems getting the results I need.

'promo_items' is a table containing a list of items on promotion
'syinvdtl' contains item detail information, by location
'syinvhis' contains sales information for items by location & date

I want to join 'syinvdtl' to 'promo_items' to get get the item details by location, there can be many locations with that item and not all locations have all items.

I then want to join to syinvhis to get the sales information for each location for a date range provided. If there is no matching sales record in the date range provided I still need at least one row containing the item,detail,location and 0's (or Nulls) for the sales information.

I have the following query:
SELECT
style,colour,width,sze,loctn,promo_items2.desc,cost,
id_curonh,id_onorder,id_bkorder,id_lrdate,id_acost,id_rprice,id_pprice,id_promid,
his_key
FROM (promo_items2 JOIN syinvdtl
ON (syinvdtl.id_style = promo_items2.style AND syinvdtl.id_colour = promo_items2.colour AND syinvdtl.id_sze = promo_items2.sze AND syinvdtl.id_width = promo_items2.width))
LEFT JOIN syinvhis
ON (promo_items2.item_key = syinvhis.his_key)
WHERE ((syinvhis.his_period='M' AND syinvhis.his_type='IT' AND syinvhis.his_loctn IN ('01','03','04','05') AND (syinvhis.his_tdate>=75424 AND syinvhis.his_tdate<=75753))
OR (syinvhis.his_period='W' AND syinvhis.his_type='IT' AND syinvhis.his_loctn IN ('01','03','04','05') AND (syinvhis.his_tdate>=75718 AND syinvhis.his_tdate<=75739)))
ORDER BY style,colour,sze,width,loctn

The query will run fine but if there is no sales record within the date range then the item is left out of the results all together.

EXPLAIN gives the following:
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","syinvdtl","ALL","PRIMARY","",,"",88749,"Using temporary; Using filesort"
1,"SIMPLE","promo_items2","ref","PRIMARY,item_index","item_index",24,"jpos.syinvdtl.id_style,jpos.syinvdtl.id_colour,jpos.syinvdtl.id_width,jpos.syinvdtl.id_sze",3,"Using where"
1,"SIMPLE","syinvhis","ref","PRIMARY,ItemKey","ItemKey",21,"jpos.promo_items2.item_key",98,"Using where"

Anyone know what I'm missing here?

 

[UltraSmooth]

After a little more digging I think I may know what is happening. The WHERE condition is breaking the LEFT JOIN because I'm placing those conditions but not allowing for Nulls in the case where there is no match on those conditions.

Could that be my problem?

 

[r937]

yes

move conditions which apply to the right table into the ON clause of the join

r937.com | rudy.ca

 

[UltraSmooth]

That was the trick, fantastic! Thanks for your help!