×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Query - Conditional SELECT with a twist

SQL Query - Conditional SELECT with a twist

SQL Query - Conditional SELECT with a twist

(OP)
I have this query

CODE

SELECT * FROM `pvt05e01adaily`  WHERE 1  
AND  ( `Expires` >= "2016/07/29" OR `Expires` = "" ) 
AND ...
AND ...
AND ...
ORDER BY `ID`  DESC  LIMIT 0, 300 

the query is dynamically built, hence the WHERE 1 and the potential AND clauses.

I need help with the ( `Expires` >= "2016/07/29" OR `Expires` = "" ) part of the query. I have an additional field "status". The expiry date only applies to status "Q" and for the life of me, I cannot get it to work.

I have changed it to:

CODE

( WHEN `status` = "Q" THEN `Expires` >= "2016/07/29" OR `Expires` = "" END ) 
but this only selects rows with status Q, which made it even worst.

I am starting to think that I may have to do a SELECT * FROM (SELECT ...) type of query but hate the possible speed compromise.

Suggestions please!

Thank you all for your assistance.

--
SouthBeach
http://www.fp2php.com
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

RE: SQL Query - Conditional SELECT with a twist

Hi

Not sure if I got it correctly at this hour, but would try it like this :

CODE --> SQL ( fragment )

AND  (( `Expires` >= "2016/07/29" AND `status` = 'Q') OR `Expires` = "" ) 

Feherke.
feherke.ga

RE: SQL Query - Conditional SELECT with a twist

(OP)
@feherke, that did not work - Nothing selected! sad

I want to select ALL rows but, if the status is Q, only if expiry date is today or greater ...

I'll deal with blank dates by putting a date way in the future - those are entries that do not expire so may as well expire them 100 years into the future.

--
SouthBeach
http://www.fp2php.com
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

RE: SQL Query - Conditional SELECT with a twist

Hi

Oh. Then your approach was good :

CODE --> SQL ( fragment )

CASE WHEN `status` = "Q" THEN `Expires` >= "2016/07/29" OR `Expires` = "" ELSE 1 END 

Feherke.
feherke.ga

RE: SQL Query - Conditional SELECT with a twist

(OP)
Looks good!

thank you ...thumbsup

--
SouthBeach
http://www.fp2php.com
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close