ElGauchooo ElGauchooo - 5 months ago 11
MySQL Question

MySQL Check if any row has a date in the future

(The following schema has been minified to reduce the problem)

I'm having two tables

Products
:

| ID | ProductName | AvailableDate (date)
|------------------------------------------------
| 1 | Foo | 2011-01-01
| 2 | Bar | 2017-01-01
| 3 | FooBar | 2011-01-01


and
Mappings
:

| ID | ProductID | SomeOtherID
|------------------------------------------------
| 1 | 1 | 1
| 2 | 2 | 1
| 3 | 3 | 2


A Mapping can point to
n
Products:

SELECT
GROUP_CONCAT(`Products`.ProductName SEPARATOR ', ')
FROM
Mappings
JOIN
Products ON Mappings.ProductID = Products.ID
GROUP BY
Mappings.SomeOtherID


I now want to extend the query to check if any of the rows in
Products
have an AvailableDate that is larger than the current Date
to spot the combined Products, that have at least one "early-access" Product.

I tried the following

SELECT
GROUP_CONCAT(`Products`.ProductName SEPARATOR ', '),
IF(COUNT(DATEDIFF(`Products`.AvailableDate, CURDATE)) > 0, 1, 0) As IsEarlyAccess
FROM
Mappings
JOIN
Products ON Mappings.ProductID = Products.ID
GROUP BY
Mappings.SomeOtherID


but i always receive
IsEarlyAccess = 1
. Any hints?

Answer

I would be tempted to use an IF on a MAX rather than COUNTing an IF:-

SELECT GROUP_CONCAT(Products.ProductName SEPARATOR ', '), IF(MAX(Products.AvailableDate) > CURDATE(), 1, 0) As IsEarlyAccess 
FROM Mappings 
JOIN Products ON Mappings.ProductID = Products.ID 
GROUP BY Mappings.SomeOtherID
Comments