Error Number: 1242 Subquery returns more than 1 row

Question

I have a table called view_att_tabulation which holds the daily attendance info of an employee for a given day & tbl_employ holds all the information about an employee. I want to retrieve the attendance of all the employees who are active=Y, whose status is 1, and whose employee_id starts with "W" on a given date. The below code always works fine for today and yesterday but always throws an error for dates before that. Any suggestion on where I have gone wrong?

SELECT emp_id, employ_id, employ_label, employ_name, employ_shortname, status, active_flag,
    (SELECT
        CASE
        WHEN (
            SELECT view_att_tabulation.emp_id FROM view_att_tabulation 
            WHERE view_att_tabulation.emp_id = tbl_employ.emp_id AND 
            employ_workdate = '2024-01-17' AND attendance_type LIKE 'PRESENT'
        )
        THEN 'PRESENT'
        WHEN
        (
            SELECT view_att_tabulation.emp_id FROM view_att_tabulation 
            WHERE view_att_tabulation.emp_id = tbl_employ.emp_id AND 
            employ_workdate ='2024-01-17' AND attendance_type LIKE 'ABSENT'
        )
        THEN 'ABSENT'
        ELSE ''
    END
   )AS employ_att
FROM tbl_employ
WHERE  active_flag = 'Y' AND status = '1' AND employ_id LIKE 'W%'
ORDER BY employ_att, `tbl_employ`.`employ_id` ASC;

Answer 1

SELECT 
    tbl_employ.emp_id,
    tbl_employ.employ_id,
    tbl_employ.employ_label,
    tbl_employ.employ_name,
    tbl_employ.employ_shortname,
    tbl_employ.status,
    tbl_employ.active_flag,
    CASE
        WHEN view_att_tabulation.attendance_type = 'PRESENT' THEN 'PRESENT'
        WHEN view_att_tabulation.attendance_type = 'ABSENT' THEN 'ABSENT'
        ELSE ''
    END AS employ_att
FROM 
    tbl_employ
LEFT JOIN 
    view_att_tabulation ON tbl_employ.emp_id = view_att_tabulation.emp_id
        AND view_att_tabulation.employ_workdate = '2024-01-17'
WHERE  
    tbl_employ.active_flag = 'Y' 
    AND tbl_employ.status = '1' 
    AND tbl_employ.employ_id LIKE 'W%'
ORDER BY 
    employ_att, tbl_employ.employ_id ASC;

replaced the subqueries with a LEFT JOIN on view_att_tabulation based on the emp_id and employ_workdate. Then, I've used a CASE statement directly in the main query to determine the employ_att value based on the attendance_type.

This should simplify your query and might help resolve the issue you're facing with dates before today or yesterday. Make sure to adapt the date condition in the WHERE clause and the view_att_tabulation.employ_workdate comparison based on your needs.