1

I am using mysql. When I use JOINS like this

SELECT u.Id,u.FirstName,u.LastName,u.Email,SUM(a.Amount) Amount FROM users u LEFT JOIN amount a  WHERE a.ExpenseDate >= '2013-05-12' GROUP BY u.Id

I have 23 users but it shows only 14 users data since the rest 9 did not enter amount. I need all the 23 users against the amount.

John Woo
  • 258,903
  • 69
  • 498
  • 492
spod
  • 406
  • 3
  • 5
  • 19

1 Answers1

1

move the condition in the ON clause,

SELECT u.Id,u.FirstName,u.LastName,u.Email,SUM(a.Amount) Amount 
FROM   users u LEFT JOIN amount a ON i.ID = a.ID
       AND a.ExpenseDate >= '2013-05-12' 
GROUP  BY u.Id

in this case, the optimizer filters records from table amount first before it will be joined on table user.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Great it worked. Thank You. What is the difference actually? could you explain please – spod May 18 '13 at 06:30
  • [more explanation on this link...](http://stackoverflow.com/questions/15706112/why-and-when-a-left-join-with-condition-in-where-clause-is-not-equivalent-to-the) – John Woo May 18 '13 at 06:31