SQL handling conflicts with WHERE clause to display irrelevant data

Question

I'm trying to make a monthly report with employee attendance case studies. I have 2 tables which are employee and attendance with the following details: employee teble attendance table

after that I created SQL view to display the date column sideways sequentially (with LEFT JOIN function). monthly_report view

from this view I can display attendance data with a specific month (with WHERE clause)

SELECT
A.employee_id AS id,
A.name,
GROUP_CONCAT(A.1) AS '1',
GROUP_CONCAT(A.2) AS '2',
GROUP_CONCAT(A.3) AS '3',
GROUP_CONCAT(A.4) AS '4',
GROUP_CONCAT(A.5) AS '5',
GROUP_CONCAT(A.6) AS '6',
GROUP_CONCAT(A.7) AS '7'
FROM monthly_report AS A
WHERE MONTH(A.date_time) = '6' AND YEAR(A.date_time) = '2023'
GROUP BY A.employee_id

result

the problem i have: I want to display a report by selecting a specific month, and displaying all employee names, but I cannot do this because by using the "where" clause as above, the employee names that appear are the data that carried out activities in that month.

is it possible to display attendance data with a specific month as above, but still display all employee names(like displaying employee name without WHERE clause). what will the query be like?

Any help appreciated.

Answer 1

Looks like you want to list all employees and show their attendance for a specific month. You need to use an SQL query that combines the employee list with the attendance data for the desired month. If there’s no attendance data for an employee, it should still show the employee's name with a placeholder, like '-'.

Perhaps something like this:

SELECT
    -- Grab the employee's ID and name from our employee list
    employees.employee_id AS EmployeeID,
    employees.name AS EmployeeName,
    
    -- Try to find attendance data for each day.
    -- If it's there, show it. If not, just put a '-'.
    COALESCE(GROUP_CONCAT(attendance_report.`1`), '-') AS Day1,
    COALESCE(GROUP_CONCAT(attendance_report.`2`), '-') AS Day2,
    COALESCE(GROUP_CONCAT(attendance_report.`3`), '-') AS Day3,
    -- and so on for each day of the month...
    
FROM
    -- Start by looking at the employee list
    employee AS employees
    
    -- Now, let’s look for attendance data that matches our employee list
    LEFT JOIN monthly_report AS attendance_report
        ON employees.employee_id = attendance_report.employee_id
        -- But hey, only for the month and year we’re interested in!
        AND MONTH(attendance_report.date_time) = 6
        AND YEAR(attendance_report.date_time) = 2023
        
-- Group everything by employee so we don’t get the same name more than once
GROUP BY
    employees.employee_id;

What's happening:

• Start with the full employee list.
• Look for matching attendance records in June 2023.
• List the attendance or show '-' if none.
• Group the results by employee to avoid duplicates.