SQL IF statement in the WHERE clause to determine next WHERE/AND-CodePudding

I want to be able to select from my table of work employees, I am able to select ALL of them just fine, but now I want to select all of them under an IF statement. IF company = 'Starbucks' THEN only show specific Starbucks records WHERE startDate > 2022-1-1, whereas all other records must remain regardless of their Company or StartDate

SELECT * FROM MyTable:
Name                    Company           StartDate

Steve Smith             Starbucks          2021-01-01
Alan Smith              Greggs             2021-04-01
Charles Gregory         Starbucks          2022-02-01
Glen Royle              Apple              2022-01-01
Brian Edwards           Starbucks          2021-05-09
James White             Greggs             2022-02-01

I have tried:

SELECT * FROM MYTABLE
WHERE Company != 'Starbucks' 
AND StartDate > '2022-1-1'
//Returns 0 Starbucks employees, and ALL records after 2022-1-1


SELECT * FROM MYTABLE
WHERE StartDate > '2022-1-1'
//Only returns records after 2022-1-1 for ALL companies

Desired result:

Name                    Company           StartDate

Alan Smith              Greggs             2021-04-01
Charles Gregory         Starbucks          2022-02-01
Glen Royle              Apple              2022-01-01
James White             Greggs             2022-02-01

As you can see from the Desired result table, the two employees that are at Starbucks, with a StartDate BEFORE 2022-01-01 are not being shown, where as everyone else remains, along with the one Starbucks worker that has a StartDate AFTER 2022-01-01

Thank you.

CodePudding user response：

You should be using proper date literals for your database. On MySQL, you would want:

SELECT *
FROM MYTABLE
WHERE Company != 'Starbucks' AND StartDate > '2022-01-01';

On SQL Server you may use:

SELECT *
FROM MYTABLE
WHERE Company != 'Starbucks' AND StartDate > '20220101';

What may have been happening with your current queries is that '2022-1-1' was being treated lexicographically as a string, which then would compare wrongly to the other dates.

CodePudding user response：

Maybe something like this:

SELECT *
FROM MYTABLE
WHERE 1 = CASE WHEN Company = 'Starbucks' AND StartDate <= '2022-01-01' THEN 0 ELSE 1 END;

SELECT *
FROM MYTABLE
WHERE Company != 'Starbucks'
UNION ALL
SELECT *
FROM MYTABLE
WHERE Company = 'Starbucks' AND StartDate > '20220101';

CodePudding user response：

I think what you need is

SELECT * FROM MYTABLE
WHERE Company != 'Starbucks' 
OR StartDate > '2022-01-01'