Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How do I display staff who are not on annual leave?

I am trying to build an annual leave program.
I have two tables the first one stores all the staff data and the second one contains the staff who are on annual leave with their start and end date.
I want this program to display staff who are not on annual leave while those on annual leave should be hidden between the start and end date of their annual leave date.
This is what I get:

enter image description here

This is what I want:

enter image description here

This is my first table which stores all the staff:

enter image description here

This is my second table which store staff on annual leave:

enter image description here

This is my code:

SELECT tbl_clients.userId, tbl_clients.userName, tbl_clients.userEmail, 
    tbl_clients.userPhone, tbl_clients.specialId 
FROM tbl_clients 
LEFT JOIN tbl_absent 
ON tbl_clients.specialId = tbl_absent.specialId 
WHERE tbl_absent.startDate = ? AND tbl_absent.endDate > ?
Tags:

2

Answers


  1. 0

    You probably want only C and D in your output since only C and D are on leave, rather than B,C,D? Let me know if that is correct.

    Fiddle

    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;
    Login or Signup to reply.
  2. 0

    SQL query can be modified

    The goal of these employees can be described as enslavement or something like that, so it should not be like this.

    NOT EXISTS ( SELECT 1 FROM tbl_absent WHERE tbl_clients.specialId = tbl_absent.specialId AND '$varCurDate' BETWEEN tbl_absent.startDate AND tbl_absent.endDate )

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search