I have three tables

  1. kit - contains kit_id and kit_type (kit type is with android or IOS)
  2. appointment - contains appointment_id, created_datetime, kid_id, created_by_id
  3. user - contains user_id, user name, email etc

I am trying to get the number of appointments used by each kit within a certain date range which I can do with a group. But I also want to extract the user details of the person who last used the kit - note each kit can have multiple users and be used for multiple appointments. I have tried to use subqueries but keep ending up with a higher count probably related to my joins.

The first part without subquery is

select k.kit_id, u.user_first_name , u.user_last_name , u.user_email  ,count(*) as number_of_rows from tbl_appointment a 
join tbl_kit k on k.kit_id =a.kit_id 
join tbl_user u on a.created_by_user_id  = u.user_id 
where a.created_datetime >'2023-07-01 00:00:00.000'
and a.created_datetime > '2023-08-01 00:00:00.000'
and k.kit_type_code ='ANDROID'
and (k.kit_is_blocked = '0' or k.kit_is_blocked is null)
group by k.kit_id,u.user_first_name,u.user_last_name , u.user_email  
order by number_of_rows desc

But this could be joining on any user and not necessarily the most user of the most recent appointment

The following is an example of what I have tried but it is wrong. If someone could tell me whats wrong I would be very grateful - note only trying to get created_by_user_id as once i have that joining to user table should be easy

    count(*) AS number_of_rows
FROM tbl_appointment a
JOIN tbl_kit k ON k.kit_id = a.kit_id
        MAX(a.created_datetime) AS most_recent_created_datetime
    FROM tbl_appointment a
    WHERE a.created_datetime > '2023-07-01 00:00:00.000'
    AND a.created_datetime < '2023-08-01 00:00:00.000'
    GROUP BY a.kit_id
) most_recent ON k.kit_id = most_recent.kit_id
LEFT JOIN tbl_user u ON most_recent.most_recent_created_datetime = a.created_datetime
WHERE k.kit_type_code = 'ANDROID'
AND (k.kit_is_blocked = '0' OR k.kit_is_blocked IS NULL)
GROUP BY a.created_by_user_id, k.kit_id
ORDER BY number_of_rows DESC;


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.