0

I am trying to execute a query to get the between data values.

I have seen some questions here but my query is more complicated.

this is the query: 

SELECT calendar.datefield AS DATE, 
IFNULL( count( lead.insertDate ) , 0 ) AS task
FROM lead 
RIGHT JOIN calendar ON ( DATE( lead.insertDate ) = calendar.datefield ) 
AND lead.lpid =  '40' 
WHERE insertDate BETWEEN '2016-01-05' AND '2016-01-23'
GROUP BY DATE

I think that the problem is in the query syntax, the query works good until i add the where row.

Gabriele Petrioli
  • 191,379
  • 34
  • 261
  • 317
DavSev
  • 1,005
  • 4
  • 22
  • 47

1 Answers1

1

You have placed the condition outside the where clause. Try this.

SELECT calendar.datefield AS DATE, 
IFNULL( count( lead.insertDate ) , 0 ) AS task
FROM lead 
RIGHT JOIN calendar ON ( DATE( lead.insertDate ) = calendar.datefield )  
WHERE insertDate BETWEEN '2016-01-05' AND '2016-01-23'
AND lead.lpid =  '40'
GROUP BY DATE
FallAndLearn
  • 4,035
  • 1
  • 18
  • 24
  • If `AND lead.lpid = '40'` is included in `WHERE` clause then, it will act like `inner join`. But he is trying for `LEFT JOIN`. – Viki888 Nov 17 '16 at 14:16
  • I can't see OP asking for left join. – FallAndLearn Nov 17 '16 at 14:30
  • Sorry it is `RIGHT JOIN`. If that condition is placed in `WHERE` clause then it will act like `INNER JOIN`. – Viki888 Nov 17 '16 at 14:34
  • I guess not. Right join returns all the rows from the right table even if there are no matches with the left table. Placing the condition in where clause have no relation with it. – FallAndLearn Nov 17 '16 at 14:38