What is the correct query for this question?-CodePudding

TABLES

users 
    id bigint
    first_name varchar(255)
    last_name varchar(255)
    email varchar(255) 

service_events
    id bigint
    name varchar(255)

service_logs
    id bigint
    approved tinyint
    user_id bigint(fk:users)
    service_event_id bigint(fk:service_events)
    deleted_at datetime(nullable)

service_log_days
    id bigint
    sevice_log_id bigint(fk:service_Logs)
    total_hours double

Question:

Return a table of users with the hour sum of their total approved, non-deleted service logs. Return a record for each student, not just those who have service log records. If a student doesn't have service logs that match the filters, they should have "0" value for their row. There should only be one row per student.

Example output table would look like:

first_name  last_name   email       total_hours
Student     One     [email protected]   0
Student     Two     [email protected]   135

I tried:

SELECT u.first_name, u.last_name, u.email, sld.total_hours
FROM users as u 
INNER JOIN service_log_days as sld 
ON ?????

What is the correct query?

CodePudding user response：

Without data example it is hard to answer , but try the following query:

SELECT u.first_name,
       u.last_name,
       u.email,
       COALESCE(sld.total_hours, 0)  as tot_hours
FROM users u    
LEFT JOIN  service_logs sl on sl.user_id=u.id   
INNER JOIN  service_log_days sld on sld.sevice_log_id=sl.id

For the part ,

Return a record for each student, not just those who have service log records. If a student doesn't have service logs that match the filters, they should have "0" value for their row. There should only be one row per student.

you need to use LEFT JOIN and COALESCE which the null values will be transormed to 0 instead.

Note if the data in service_log_days are repeated for a user change

COALESCE(sld.total_hours, 0)

COALESCE(SUM(sld.total_hours), 0)

and add u.first_name,u.last_name,u.email in the group by clause.

CodePudding user response：

Join the tables. Use outer joins, as a user may not have service logs or no hours associated. When outer joining the service logs, only join those rows that are approved (approved = 1) and not deleted (deleted_at is null). At last group by user and count.

select u.*, coalesce(sum(sld.total_hours), 0) as total
from users u
left outer join service_logs sl on sl.user_id = u.id
                               and sl.approved = 1
                               and sl.deleted_at is null
left outer join service_log_days sld on sld.sevice_log_id = sl.id
group by u.id
order by u.id;

CodePudding user response：

Given the images u posted, the join should be on the id, as it is the column stays the same between different tables. So it should be u.id == sld.id and remember to use the IFNULL function to replace the null value with 0 W3schools tutorial