MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;

Table 'fiddle.V$VERSION' doesn't exist


-- Create the table
CREATE TABLE users (
    userid INT PRIMARY KEY,         
    username VARCHAR(255),          
    useremail VARCHAR(255),         
    userphone VARCHAR(20),          
    specialid INT                   
);

-- Insert the values
INSERT INTO users (userid, username, useremail, userphone, specialid)
VALUES
(1, 'staffA', 'staffA@gmail.com', '77', 123),
(2, 'staffB', 'staffB@gmail.com', '58', 345),
(3, 'staffC', 'staffC@gmail.com', '57', 567),
(4, 'staffD', 'staffD@gmail.com', '88', 987);

Records: 4  Duplicates: 0  Warnings: 0


CREATE TABLE absent (
    userid INT,                       
    username VARCHAR(255),              
    specialid INT,                    
    startdate DATE,                    
    enddate DATE,                     
    FOREIGN KEY (userid) REFERENCES users(userid)  
);


INSERT INTO absent (userid, username, specialid, startdate, enddate)
VALUES
(1, 'staffA', 123, '2024-12-02', '2024-12-03'),
(2, 'staffB', 345, '2024-12-02', '2024-12-05');

Records: 2  Duplicates: 0  Warnings: 0


SELECT u.userid, u.username, u.useremail, u.userphone, u.specialid
FROM users u
LEFT JOIN absent a
  ON u.userid = a.userid 
  AND CURDATE() BETWEEN a.startdate AND a.enddate  
 WHERE a.userid IS NULL;  


userid
username
useremail
userphone
specialid


3
staffC
staffC@gmail.com
57
567

4
staffD
staffD@gmail.com
88
987