if conditions in an SQL query-CodePudding

I have the following SQL Query

SELECT l.LoginName ,p.name as project_name, p.start_date as 
project_start_date, p.end_date as project_end_date, 
pe.budget, pe.start_date, pe.end_date  
FROM project_expenses_new pe 
INNER JOIN projects p ON  pe.project_uuid=p.uuid 
INNER JOIN LoginUsers l ON pe.employee_uuid=l.uuid 
WHERE pe.employee_uuid='8a373382-c921-11ec-a7c7-005056020962' AND 
('2021-08-01' BETWEEN  p.start_date AND p.end_date);

Basically I am using 3 tables but only 2 of those tables is relevant to the query. These tables are projects and projects_expenses_new. The two tables both have the fields start_date and end_date.

What I am trying to do is to check if the values of start_date and end_date in the project_expenses_new table has a non null value. in this case I will check if the date i am looking for is between the start_date and end_date of the project_expenses_new table. So if the value of pe.start_date and pe.end_date are not null the query will be:

SELECT l.LoginName ,p.name as project_name, p.start_date as 
project_start_date, p.end_date as 
project_end_date, pe.budget, pe.start_date, pe.end_date  
FROM project_expenses_new pe 
INNER JOIN projects p ON  pe.project_uuid=p.uuid 
INNER JOIN LoginUsers l ON pe.employee_uuid=l.uuid 
WHERE pe.employee_uuid='8a373382-c921-11ec-a7c7-005056020962' AND 
('2021-08-01' BETWEEN  pe.start_date AND pe.end_date);

If, on the other hand, the values of start_date and end_date on pthe project_expenses_new table is null, I want to query condition get the records that are between the project table start_date and end_date. These values are never NULL.

I am not sure how to incorporate an if condition in my query.

CodePudding user response：

You can use IFNULL() to use one date if the other date is NULL.

WHERE pe.employee_uuid = '8a373382-c921-11ec-a7c7-005056020962'
AND '2021-08-01' BETWEEN IFNULL(pe.start_date, p.start_date) AND 
                         IFNULL(pe.end_date, p.end_date)

CodePudding user response：

If you must check whether both start_date and end_date are null or not:

SELECT l.LoginName ,p.name as project_name, p.start_date as 
project_start_date, p.end_date as project_end_date, 
pe.budget, pe.start_date, pe.end_date  
FROM project_expenses_new pe 
INNER JOIN projects p ON  pe.project_uuid=p.uuid 
INNER JOIN LoginUsers l ON pe.employee_uuid=l.uuid 
WHERE pe.employee_uuid='8a373382-c921-11ec-a7c7-005056020962' AND 

'2021-08-01' BETWEEN (case when p.start_date is null or p.end_date is null then 
pe.p.start_date else p.start_date end) and (case when p.start_date is null or p.end_date is null then pe.p.end_date else p.end_dateend);