Mattallurgy Mattallurgy -4 years ago 43
MySQL Question

(My)SQL: If subquery is not an empty set, return subquery

This question is directed specifically toward MySQL, but I'm trying to ask it in such a way that standard SQL is applicable.

Context: I am trying to determine an end date in the following way: if there exists another start date after the entered start date, use the existing start date as the end date; otherwise, the end date should be 30 days after the entered start date.

The solution I've tried is similar to the following:

SELECT
IF(
EXISTS(
SELECT
DISTINCT start_date
FROM table
WHERE ? < start_date AND
identifier = ?
ORDER BY start_date
LIMIT 1
), (
SELECT
DISTINCT start_date
FROM table
WHERE ? < start_date AND
identifier = ?
ORDER BY start_date
LIMIT 1),
DATE_ADD(?, INTERVAL 30 DAY)
) AS end_date


My solution works, but I was hoping there were a more elegant, non-repetitive solution.

The generic solution would be one which—if a subquery exists—returns the values from the subquery; otherwise, something else can be returned.

Answer Source

In response to your own answer I'd suggest to use:

SELECT
  COALESCE((
    SELECT MIN(start_date)
    FROM TABLE
    WHERE start_date > ? 
    AND   identifier = ?), (
    SELECT
      DATE_ADD(?, INTERVAL 30 DAY)
    )) AS end_date 

Seems easier to understand IMHO. And even though it looks different, it pretty much does the same things behind the scenes.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download