SELECT   
  p.code sku_style_10_left
, (p.NAME) title_style_40_left
,p.description as Description_style_15_left
,(ifnull( pi.qty_in_stock,0))   on_Hand_qty_sum_6_style_15_right  
 ,(ifnull( pd.field_value,0))   on_Reorder_qty_style_10_right  
, p.updated_on as Last_Update_style_10_left
,  p.category_id
,tbl_productcategory.category


FROM      tbl_products p
inner join tbl_productcategory on tbl_productcategory.productCategory_id = p.category_id
LEFT JOIN  tbl_inventory pi ON         p.product_id=pi.product_id
left join tbl_product_details pd on  p.product_id=pd .parent_id and pd.field_name = 'reorder_qty'
where  ( p.category_id = case when  {category_id} = 0 then 1 else   {category_id} end )
and (ifnull( pi.qty_in_stock,0)) <= ifnull( pd.field_value,0)
and p.is_active = 1 and p.is_delete = 0
ORDER BY   p.category_id,p.NAME,
             p.code