Is there a way where I can filter/get in between dates when in MYSQL IN Clause that handles multiple-CodePudding

So recently I have discovered that using the In Clause In MYSQL 5.7, allows me to extract specific data base on multiple columns.

SELECT * FROM `Schema`.`Table` where (`ID`,`Code`) IN ((1,'AE'),(2,'AD'));

ID	Code	Date
1	AE	2021-01-01
1	AE	2021-01-02
1	AE	2021-01-03
1	AE	2021-01-04
2	AD	2021-01-01
2	AD	2021-01-02
2	AD	2021-01-03
2	AD	2021-01-04

This is amazing, But if I want to include an additional column for a particular range of Dates, it does not work. Here is the following query I have attempted.

SELECT * FROM `Schema`.`Table` WHERE (`ID`,`Code`,`Date`) 
IN ((1,'AE', BETWEEN '2021-01-01' AND '2021-01-03'),(2,'AD',BETWEEN '2021-01-02' AND '2021-01-04'));

I could use Union ALL to merge the unique Data on the ID and Code and have the separate Date Range. But I am just wondering if my approach for the syntax is wrong, or even if there is a better way to approach this.

Thank you in advance

CodePudding user response：

You can can do it using >= and <=.

SELECT *
FROM `Schema`.`Table`
WHERE (ID, Code) IN (1, 'AE') AND Date >= '2021-01-01' AND Date <= '2021-01-03' 
      OR
      (ID, Code) IN (2, 'AD') AND Date >= '2021-01-02' AND Date <= '2021-01-04';

CodePudding user response：

You could express this using a hybrid approach as:

SELECT *
FROM `Schema`.`Table`
WHERE (ID, Code) IN ((1, 'AE')) AND Date BETWEEN '2021-01-01' AND '2021-01-03' OR
      (ID, Code) IN ((2, 'AD')) AND Date BETWEEN '2021-01-02' AND '2021-01-04';