用户登录校验当日考勤状态：双表关联查询实现咨询

Hey there! Let's work through fixing your SQL query to get the exact attendance status you need when a user logs in.

First, let's break down the gaps in your original query:

• You used an implicit inner join (comma-separated tables), which only returns users who have matching attendance records. We need a LEFT JOIN to guarantee we get user data even if there's no attendance entry for the day.
• You didn't filter create_date to the current day—we only care about whether the user checked in today, not any past records.
• The ifnull(a.attendance_id, 0) logic doesn't correctly signal "has attendance today"—we need to check if any matching daily attendance record exists, not just if the ID is null.

Here's the corrected query that meets your requirement:

SELECT 
    u.user_id,
    u.name,
    u.username,
    -- Check if a daily attendance record exists, return 1 or 0
    IF(a.att_id IS NOT NULL, 1, 0) AS attendance
FROM 
    tbl_user u
LEFT JOIN 
    tbl_attendance a 
    ON u.user_id = a.user_id 
    -- Only match attendance entries from today
    AND DATE(a.create_date) = CURDATE()
WHERE 
    u.username = "emp1" 
    AND u.password = "password@123" 
    AND u.role <> 'customer';

Key Details to Understand: #

• LEFT JOIN: This ensures we always pull the user's data from tbl_user, even if there's no corresponding attendance entry for today in tbl_attendance.
• Date Filter in JOIN Clause: We put DATE(a.create_date) = CURDATE() in the JOIN (not the WHERE clause) because putting it in WHERE would turn the LEFT JOIN into an inner join (NULL dates wouldn't match CURDATE(), so users without attendance would be excluded).
• IF Condition: This checks if a matching daily attendance record exists. If yes, att_id will have a value so we return 1; if no, att_id will be NULL so we return 0.

If you prefer more explicit syntax, here's an alternative using a CASE statement:

SELECT 
    u.user_id,
    u.name,
    u.username,
    CASE 
        WHEN a.att_id IS NOT NULL THEN 1 
        ELSE 0 
    END AS attendance
FROM 
    tbl_user u
LEFT JOIN 
    tbl_attendance a 
    ON u.user_id = a.user_id 
    AND DATE(a.create_date) = CURDATE()
WHERE 
    u.username = "emp1" 
    AND u.password = "password@123" 
    AND u.role <> 'customer';

Quick Notes: #

• Double-check that the role field exists in tbl_user (your original query references it, so we kept it in the WHERE clause).
• If your create_date uses time zones, adjust the date comparison to match your local time (e.g., DATE(CONVERT_TZ(a.create_date, 'UTC', 'Asia/Shanghai')) = CURDATE() if needed).

内容的提问来源于stack exchange，提问作者snehal