Most efficient MySQL query for not Exists and Sum

Question

I'm not quite sure what I'm doing wrong here.

I have this query where I want to fetch u.id if the user has over a set amount of hours registered, and does not already have a salary increase registered. In this case only 1 should be the output, because there is no registered rate_custom and num_hours is more than the custom_hours that is 1000

I have 3 tables:

This is what I got so far, but I tried parts of it without any luck, so I'm just wondering if there is a faster and more efficient way to deal with this, and one that is actually working?

"SELECT u.id "
        . "FROM users u "
        . "LEFT JOIN user_hours uh "
        . "ON u.id = uh.user_id "
        . "LEFT JOIN rate_custom rc "
        . "ON u.id = rc.user_id "
        . "WHERE "
        . "NOT EXISTS (r.crate_id FROM rate_custom r WHERE r.date_added <= NOW() AND r.date_ended IS NULL AND r.user_id = u.id) "
        . "GROUP BY u.id "
        . "HAVING SUM(("
        . "SELECT (THE NUM HOURS WHERE HOUR STATUS IS 4) "
        . ")) >= ("
        . "SELECT rcc.custom_hours FROM rate_custom rcc WHERE rcc.date_ended IS NULL ORDER BY rcc.crate_id DESC LIMIT 1"
        . ")";

Answer 1

If I understand the logic correctly, you can do:

SELECT u.id 
FROM users u LEFT JOIN
     user_hours uh
     ON u.id = uh.user_id LEFT JOIN
     rate_custom rc
     ON u.id = rc.user_id 
WHERE rc.user_id IS NULL
GROUP BY u.id
HAVING SUM(CASE WHEN hour_status = 4 THEN num_hours ELSE 0 END) >= 
           (SELECT rcc.custom_hours FROM rate_custom rcc WHERE rcc.date_ended IS NULL ORDER BY rcc.crate_id DESC LIMIT 1);

You don't need a subquery to find the non-matches to rate_custom; the LEFT JOIN does that. For the HAVING clause you can use CASE.