2

I have 3 tables( products,bill_Details and bills) , i want to retrieve quantity of each product in bill_details table, the below query just give me the products which are in bill_details table "just", and if bill_details empty, nothing retrieved !

enter image description here

and this is my query:

select p.prod_Id,p.prod_Name,
sum(b.de_Quantity+b.de_Bonus) - sum(bbb.de_Quantity+bbb.de_Bonus),
p.prod_Cost,p.prod_ExpDate,p.prod_BonusInfo,p.prod_Note 

from

(((products p left JOIN bill_Details b on p.prod_Id=b.prod_Id) 
left JOIN bill_Details bbb on p.prod_Id=bbb.prod_Id )
left JOIN bills a on b.bill_Id = a.bill_Id) 
left JOIN bills aaa on bbb.bill_Id = aaa.bill_Id

where 

a.cus_Sup=1 and aaa.cus_Sup=0 and a.archived=0 and aaa.archived=0 
group by p.prod_Id,p.prod_Name,p.prod_Cost,p.prod_ExpDate,
p.prod_BonusInfo,p.prod_Note order by p.prod_Name asc;
Anik Islam Abhi
  • 25,137
  • 8
  • 58
  • 80
Belal Ghanem
  • 97
  • 1
  • 11

1 Answers1

0

Your WHERE clause is checking against tables that are left joined. By doing that, you are effectively removing rows that are unmatched in the left joins. Probably not what you want.

Instead, what you want is probably to move those conditions in the join clauses themselves, like this:

select p.prod_Id,p.prod_Name,
sum(b.de_Quantity+b.de_Bonus) - sum(bbb.de_Quantity+bbb.de_Bonus),
p.prod_Cost,p.prod_ExpDate,p.prod_BonusInfo,p.prod_Note 

from

(((products p left JOIN bill_Details b on p.prod_Id=b.prod_Id) 
left JOIN bill_Details bbb on p.prod_Id=bbb.prod_Id )
left JOIN bills a on b.bill_Id = a.bill_Id and a.cus_Sup=1 and a.archived=0) 
left JOIN bills aaa on bbb.bill_Id = aaa.bill_Id and aaa.cus_Sup=0 and aaa.archived=0

group by p.prod_Id,p.prod_Name,p.prod_Cost,p.prod_ExpDate,
p.prod_BonusInfo,p.prod_Note order by p.prod_Name asc;
sstan
  • 35,425
  • 6
  • 48
  • 66
  • thanx @sstan , but is this situation with also other types of join ? – Belal Ghanem Jul 04 '15 at 03:02
  • With normal joins, there is no difference where you put the condition, afaik. Though I personally try to get in the habit of putting them in the join condition, so that I don't run into bad surprises when I suddenly mix in left joins. – sstan Jul 04 '15 at 03:04
  • Is there any resource or article about this rule to understand it clearly ?! – Belal Ghanem Jul 04 '15 at 03:13
  • If you google `left join where vs on` you'll find some good reading. Here are a few links: [link1](http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause) and [link2](http://www.codeproject.com/Articles/231132/Difference-between-And-clause-along-with-on-and-Wh). – sstan Jul 04 '15 at 03:19