Member Avatar for jacob21

Below tables :I am trying to calculate
openLeadsCount having status 1

I tried but getting wrong results

SELECT COUNT(lead.id) FROM lead JOIN lead_status ls WHERE ls.lead_id=lead.id 
AND ls.id=(SELECT MAX(id) FROM lead_status lst WHERE lst.status=1 )




Lead table:
    id created_on
    1    2014-1-13 
    2    2014-1-18

lead_status table
    id lead_id   created_on       lead_status
    1    1       2014-01-13:05:00      1       
    2    1       2014-01-14:06:00      2
    3    1       2014-01-14:06:00      1
    4    2       2014-01-14:06:00      1
  • 2 Contributors
  • 1 Reply
  • 140 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by Reverend Jim
Member Avatar for Reverend Jim

Try

SELECT COUNT(lead.id) 
  FROM lead JOIN lead_status ls 
    ON ls.lead_id=lead.id 
   AND ls.id=(SELECT MAX(id) FROM lead_status WHERE lead_status=1)

Note the replacement of WHERE with ON and the correction of the field name in lst.status=1 to lead_status=1.

This is a valid query, however, I am not sure this is the query you want because I don't know what constitutes an openLead and you didn't give an example of the recordset you wanted returned.

Edited by Reverend Jim
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.