I have 3 tables named
- com_event_schedules
- com_appointments
- com_event_schedules_com_appointment_c
which has a relation between first two tables.
Following are the fields of the tables
com_event_schedules -- id -- name -- schedule_date -- start_time -- end_time -- deleted
com_appointments -- id -- start_time -- end_time -- status
com_event_schedules_com_appointment_c -- id -- com_event_schedules_com_appointmentcom_event_schedules_ida (schedule_id) -- com_event_schedules_com_appointmentcom_appointment_idb (appointment_id)
relation between tables com_event_schedule and com_appointments is 1 to Many
What I want result having schedule_id, and total counts of its appointments on condition status='completed'
I tried following query:
SELECT sch.id,COUNT(app.id) AS total,
(SELECT COUNT(ap.id)
FROM
com_appointment ap,
com_event_schedules sc,
com_event_schedules_com_appointment_c re
WHERE
re.com_event_schedules_com_appointmentcom_event_schedules_ida=sc.id AND
ap.id=re.com_event_schedules_com_appointmentcom_appointment_idb AND
sc.deleted=0 AND
ap.status='completed') AS completed
FROM
com_event_schedules sch,
com_appointment app,
com_event_schedules_com_appointment_c rel
WHERE
rel.com_event_schedules_com_appointmentcom_event_schedules_ida=sch.id AND
app.id=rel.com_event_schedules_com_appointmentcom_appointment_idb AND
sch.deleted=0 GROUP BY sch.id
Using this query Im getting accurate total count but completed count is not as expected. it is showing 1 for each schedule. However only 1 appointment in db is completed and others are still pending.
Is there something wrong with query ?? I have SugarCRM in backend. Cant use fiddle cause relation and fields are too messy.