Mysql query: where clause if exists
I have a MySQL db and inside it a ProductMaster table with 6 fields "Product_Id, Product_Name, Model, Opening_date, Closing_Date, Status". Opening and Closing Date The date can be null. Some records have values for this field
I have the following query to display records from this table.
"select Product_Id,Product_Name,Model from ProductMaster where Status=1"
Now I want to change the request for the presence of a filter in the Open_Date and Closing_Date files if they are not null
Example: If an entry has Open_Date as 05/01/2009 and Closing Date as 05/30/2009 Then I want to check if today's date is between these two dates, and if Yes, "Returns entries"
If both field values are empty, the records returned
Can anyone help me create a request? thanks in advance
a source to share
The query part WHERE
(clause WHERE
) is just a long boolean expression. What does it mean? That MySQL just wants it to return either true or false, which it interprets as "Yes. Include this line in the results." or "No. Do not include this line in the results."
If I understand correctly, you want to do two things:
- Check if
opening_date
andclosing_date
null
- Check if today is between these two dates.
And you only want # 2 to execute if # 1 is true. This can be expressed as follows:
#1 AND #2
Which would evaluate to false if either # 1 or # 2 is false.
This can be translated as:
-
(opening_date IS NOT NULL) AND (closing_date IS NOT NULL)
-
NOW() >= opening_date AND NOW <= closing_date
So if we consider those two as # 1 and # 2 in the expression we said, we will use ( #1 AND #2
), then we get:
((opening_date IS NOT NULL) AND (closing_date IS NOT NULL))
AND (NOW() >= opening_date AND NOW <= closing_date`)
And this is the offer WHERE
you need.
a source to share