2

I have 3 tables named

  1. com_event_schedules
  2. com_appointments
  3. com_event_schedules_com_appointment_c

which has a relation between first two tables.

Following are the fields of the tables

  1. com_event_schedules -- id -- name -- schedule_date -- start_time -- end_time -- deleted

  2. com_appointments -- id -- start_time -- end_time -- status

  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 (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.

Star
  • 3,222
  • 5
  • 32
  • 48
Mahendra
  • 908
  • 1
  • 18
  • 38
  • you should deffinatly have a deeper look into `GROUP BY` - not only that this will void your subselect but is way more elegant. For your problem, you most likely need to match the `com_event_schedules sc` from your inner select with the one `com_event_schedules sch` from your outer select – Najzero Jun 07 '13 at 06:35
  • @ Najzero : I tried with out using GROUP BY it wasn't given a total count correct. – Mahendra Jun 07 '13 at 06:37
  • If you have one-to-many relationship, you don't need to create another table for the relationship. It is straight forward to simply take the primary key of the first table('one' side) and put it in the second table ('many' side) as a foreign key. – Bere Jun 07 '13 at 06:40
  • @ Bere : I wish I could have option. Its SugarCRM in backend which creates table structure automatically. I don't know why my company even using it. – Mahendra Jun 07 '13 at 06:41
  • Well as commented before but saying explicitly again: your subquery has no connection to your "outer" one, most likely it is enough to add another `AND sc.id = sch.id` as a clause to the inner one ( or any other matching between outer select and inner query ) – Najzero Jun 07 '13 at 06:43
  • SELECT COUNT(ap.id) FROM com_appointment ap, com_event_schedules sc WHERE sc.id=sch.id AND ap.therapy_status='gray') AS count2 This is my inner query now. But result it still same :( – Mahendra Jun 07 '13 at 06:47
  • Possible duplicate of [Not getting correct COUNT() in MySQL Query (Modified Earlier )](https://stackoverflow.com/questions/16978320/not-getting-correct-count-in-mysql-query-modified-earlier) – Star Nov 20 '17 at 09:53

1 Answers1

1

This query should be able to help you. The biggest thing it does is count ALL of the appointments for total and then SUM on an IF status = completed to get you both the total and the completed in the same query.

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

Also, I was noticing you said that it was a One to Many relationship. Relational tables like you have are really for Many to Many. The most efficient way to have a One to Many is to get rid of the com_event_schedules_com_appointment_c table and add a com_event_schedule_id to the com_appointments table.

chrislondon
  • 12,487
  • 5
  • 26
  • 65