I need to check and get record id list if some dates list is between start date and end date in one query mysql

Question

example // dates list '2022-2-24' ,'2022-2-27' , '2022-2-28','2022-3-01' is between start_date column and end_date column query

select id from leaves 
where '2022-2-24' ,'2022-2-27' , '2022-2-28','2022-3-01' between start_date  and end_date 

Answer 1

You can use the following style, however, your query gets longer.

SELECT id FROM leaves 
WHERE start_date >= '2022-02-24' AND end_date <= '2022-02-24' 
OR start_date >= '2022-02-27' AND end_date <= '2022-02-27'
OR start_date >= '2022-02-28' AND end_date <= '2022-02-28'
OR start_date >= '2022-03-01' AND end_date <= '2022-03-01'

Answer 2

You haev to check all dates singularly

CREATE TABLE leaves (id int,start_date date,end_date date )

INSERT INTO leaves VALUES (1, '2022-02-02', '2022-03-02'),(2, '2022-01-02', '2022-02-02'),(3, '2022-01-02', '2022-03-10')

select id from leaves 
where '2022-2-24' between start_date  and end_date 
OR '2022-2-27' between start_date  and end_date  
OR '2022-2-28' between start_date  and end_date 
OR '2022-3-01' between start_date  and end_date 

| id |
| -: |
|  1 |
|  3 |

select id from leaves 
where '2022-2-24' between start_date  and end_date 
UNION
select id from leaves 
where '2022-2-27' between start_date  and end_date  
UNION
select id from leaves 
where '2022-2-28' between start_date  and end_date 
UNION
select id from leaves 
where '2022-3-01' between start_date  and end_date 

| id |
| -: |
|  1 |
|  3 |

db<>fiddle here