CODEWITHSUNDEEP
mysqlsql
Mattallurgy
Mattallurgy

Reputation: 72

(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.

Upvotes: 0

Views: 1659

Answers (3)

deroby
deroby

Reputation: 6002

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.

Upvotes: 1

Mattallurgy
Mattallurgy

Reputation: 72

As per fancyPants' solution, I used COALESCE, but in a radically different way (hence why I can't entirely mark the response as accepted).

SELECT
  COALESCE((
    SELECT
      DISTINCT start_date
    FROM TABLE
    WHERE ? < start_date AND
          identifier = ?
    ORDER BY start_Date
    LIMIT 1), (
    SELECT
      DATE_ADD(?, INTERVAL 30 DAY)
    )) AS end_date

The above query will do precisely as intended. Your subquery is the first argument of the COALESCE statement, and the alternative query is the second.

Upvotes: 0

fancyPants
fancyPants

Reputation: 51928

Instead of a subquery do a left join (to the table itself)

SELECT
COALESCE(t2.start_date, t1.start_date)
FROM table t1
LEFT JOIN table t2 ON t1.identifier = t2.identifier AND t1.start_date > t2.start_date

The left joined entry is either there or it is not, which means it is not null or null. The COALESCE() function returns the first of its arguments which is not null.

Upvotes: 2

Related Questions