MySQL Запросить два разных условия для разных счетчиков в 1 запросе

У меня есть 3 таблицы с именем

  1. com_event_schedules
  2. com_appointments
  3. com_event_schedules_com_appointment_c

которая имеет отношение между первыми двумя таблицами.

Ниже приведены поля таблиц

  1. com_event_schedules – id – name – schedule_date – start_time – end_time – удален

  2. com_appointments – id – start_time – end_time – статус

  3. com_event_schedules_com_appointment_c – id – com_event_schedules_com_appointmentcom_event_schedules_ida (schedule_id) – com_event_schedules_com_appointmentcom_appointment_idb (destination_id)

отношение между таблицами com_event_schedule и com_appointments равно 1 для многих

То, что я хочу получить с помощью sched_id, и общее количество его назначений при условии status = 'completed'

Я попробовал следующий запрос:

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 

Используя этот запрос, я получаю точный общий счет, но завершенный счет не так, как ожидалось. он показывает 1 для каждого расписания. Однако только 1 назначение в db завершено, а другие все еще находятся на рассмотрении.

Что-то не так с запросом? У меня есть SugarCRM в backend. Не могу использовать скрипт, потому что поля и поля слишком грязные.

Этот запрос должен помочь вам. Самое большое, что он делает, это подсчитать ВСЕ назначения для общего числа, а затем SUM по статусу IF = завершено, чтобы получить как общее, так и завершенное в том же запросе.

 SELECT sc.id, COUNT(ap.id) as total, SUM(IF(status = 'completed', 1, 0)) as completed FROM com_event_schedules sc LEFT JOIN com_event_schedules_com_appointment_c re ON re.com_event_schedules_com_appointmentcom_event_schedules_ida = sc.id LEFT JOIN com_appointment ap ON re.com_event_schedules_com_appointmentcom_appointment_idb = ap.id WHERE sc.deleted = 0 GROUP BY sc.id 

Кроме того, я заметил, что вы сказали, что это отношения от одного до многих. Реляционные таблицы, как и у вас, действительно для многих для многих. Самый эффективный способ иметь от одного до многих – избавиться от таблицы com_event_schedules_com_appointment_c и добавить com_event_schedule_id в таблицу com_appointments .