I have a query with a where condition like so:
WHERE A.event_date BETWEEN B.start_date AND B.end_date
The complexity is that if B.start_date is NULL, it means from time immemorial, similarly B.end_date is NULL, it means the present. So I still want to select a row where A.event_date > B.start_date and B.end_date is NULL, for example.
The long-winded way to do this is
WHERE A.event_date BETWEEN B.start_date AND B.end_date
OR (A.event_date > B.start_date AND B.end_date IS NULL)
OR (B.start_date IS NULL AND A.event_date < B.end_date)
Is there a more elegant solution (especially since in one query I have multiple between condition like that)
IFNULL might help out here:
WHERE A.event_date BETWEEN IFNULL(B.start_date,"1900-01-01")
AND IFNULL(B.end_date,now());