SQL Query - Conditional SELECT with a twist 1

[southbeach]

I have this query

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:

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

 

[feherke]

Hi

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

[b]AND[/b]  [teal](([/teal] `Expires` [teal]>=[/teal] [i][green]"2016/07/29"[/green][/i] [b]AND[/b] `status` [teal]=[/teal] [i][green]'Q'[/green][/i][teal])[/teal] [b]OR[/b] `Expires` [teal]=[/teal] [i][green]""[/green][/i] [teal])[/teal]

Feherke.
feherke.ga

 

[southbeach]

@feherke, that did not work - Nothing selected! :-(

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.

 

[feherke]

Hi

Oh. Then your approach was good :

[b]CASE[/b] [b]WHEN[/b] `status` [teal]=[/teal] [i][green]"Q"[/green][/i] [b]THEN[/b] `Expires` [teal]>=[/teal] [i][green]"2016/07/29"[/green][/i] [b]OR[/b] `Expires` [teal]=[/teal] [i][green]""[/green][/i] [highlight][b]ELSE[/b] [purple]1[/purple][/highlight] [b]END[/b]

Feherke.
feherke.ga

 

[southbeach]

Looks good!

thank you ...

--
SouthBeach

http://www.fp2php.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.