CODEWITHSUNDEEP
mysqlcase-when
user3243925
user3243925

Reputation: 271

MySQL if variable then show results + specific result

Table clients

ID     |   Name    | start_Date          |  Future
1          John      20-02-2014 15:00:00     0
2          Maria     20-02-2014 15:00:00     0
3          Stack     21-02-2014 15:00:00     1
4          Google    21-02-2014 15:00:00     0

Currently, my query looks like this:

SELECT * FROM clients

Obviously this will get all clients, but I want to display the clients according to the Future variable. When Future variable is set the clients can only show from the start_Date to further.

If I change my query to this:

SELECT * 
FROM clients
WHERE (NOW() >= start_Date OR start_Date IS NULL
       OR start_Date = '0000-00-00 00:00:00')

This will get me:

ID     |   Name    | start_Date          |  Future
1          John      20-02-2014 15:00:00      0
2          Maria     20-02-2014 15:00:00      0

But the output I expect is:

ID     |   Name    | start_Date          |  Future
1          John      20-02-2014 15:00:00     0
2          Maria     20-02-2014 15:00:00     0
4          Google    21-02-2014 15:00:00     0

I don't want to show

3          Stack     21-02-2014 15:00:00      1

because the variable Future is set to 1 and it can only be shown tomorrow (21-02-2014 15:00:00). So, finally, I've come up with this without success:

SELECT * 
FROM clients
WHERE CASE WHEN Future = 1 THEN (NOW() >= start_Date OR start_Date IS NULL
       OR start_Date = '0000-00-00 00:00:00') END

Upvotes: 0

Views: 47

Answers (2)

echo_Me
echo_Me

Reputation: 37233

why not use this

 SELECT * 
 FROM clients
 WHERE future = 0

Upvotes: 0

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try this:

SELECT * 
FROM clients
WHERE future = 0
OR (future = 1 
    AND (NOW() >= start_Date 
         OR start_Date IS NULL 
         OR start_Date = '0000-00-00 00:00:00'))

Upvotes: 2

Related Questions