I have house_leases and house_lease_terms (see table schemas below). A house_lease can have multiple house_lease_terms however there can only be one "active" term at a time.
CREATE TABLE `house_leases` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `house_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `house_lease_terms` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `house_lease_id` int(10) unsigned NOT NULL, `date_start` datetime NOT NULL, `date_end` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `house_lease_terms_house_lease_id_foreign` (`house_lease_id`), CONSTRAINT `house_lease_terms_house_lease_id_foreign` FOREIGN KEY (`house_lease_id`) REFERENCES `house_leases` (`id`) ON DELETE CASCADE );
As you can see the house_lease_terms.house_lease_id corresponds to a specific house_lease, however there can be multiple rows with the same house_lease_id.
The rules for determining the "active" terms are:
date_start <= NOW() AND date_end > NOW() OR date_end IS NULL
If no rows are returned then the "active" terms must be in the future, so then the rules change to be:
date_start > NOW()
We order by date_start DESC if the terms are not in the future since multiple rows could be returned we want the latest date_start at the top of the results. Otherwise we sort by date_start ASC because we want the closest date_start to now to be at the top.
I then limit by 1 to get only one result and that row is considered the "active" terms. If no results come back, then there are no "active" terms.
I have a SQL statement that has this logic for getting a specific house_lease_id. That looks like this:
SELECT * FROM house_lease_terms WHERE CASE WHEN (SELECT COUNT(*) FROM house_lease_terms WHERE date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL) AND house_lease_id = 1) THEN date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL) ELSE date_start > NOW() END AND house_lease_id = 1 ORDER BY IF( (SELECT COUNT(*) FROM house_lease_terms WHERE date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL) AND house_lease_id = 1), unix_timestamp(date_start), -unix_timestamp(date_start) ) DESC LIMIT 1;
This statement works, but I wish there was a better way (more efficient) of fetching the "active" terms for a specific house_lease_id (If you know a better solution please share).
Now I need to have a query that will fetch the "active" terms for all the different house_lease_id's.
I don't want any type of custom MySQL function or stored procedure to do this. I don't know where to start to create this query. I figure I can use the query from above in some sub select or join, but am not sure how I would do so.
Any help will be appreciated!
SQLFiddle with data: http://sqlfiddle.com/#!9/cab159/2/0