1

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.

Table Definitions:

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

1
  • First do a JOIN, then do a "groupwise max".
    – Rick James
    Jan 24, 2020 at 22:43

2 Answers 2

0

Must be missing something, but isn't this simply what you want?

SELECT house_lease_id, MIN(date_start)
FROM house_lease_terms
WHERE date_end > NOW() OR date_end IS NULL
GROUP BY house_lease_id

The earliest term that hasn't ended yet.

0

Answer credit goes to Akina over at StackOverflow

SELECT *
FROM (
SELECT *,
       CASE WHEN @var2=house_lease_id 
            THEN @var1:=@var1+1 
            ELSE @var1:=1
            END row_number_in_house_lease_id,
       @var2:=house_lease_id
FROM house_lease_terms, (SELECT @var1:=0, @var2:=0) variables 
WHERE (
        (date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL))
        OR (date_start > NOW())
      )
ORDER BY 
  house_lease_id,
  CASE WHEN (date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL))
  THEN DATEDIFF(NOW(), date_start)
  ELSE DATEDIFF(date_start, NOW()) + 1000000
  END,
  id DESC
) AS cte
WHERE row_number_in_house_lease_id = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.