CODEWITHSUNDEEP
mysqlsqlcase
Colin Dupree
Colin Dupree

Reputation: 13

Using a case/if in the where clause SQL

So I have a list of items organized by date in two different categories, when switching the categories I sometimes run into an error that does not let the item go into the correct placement. The thing that is messing up the query is what I want to place in a case/if statement becuase it is only needed if there is an item with the same date, anyother time it throws off the whole query. Here is what I have, granted i know that that case does not work where it is or how it is, please work with me.

SELECT CASE WHEN COUNT(*)=0 THEN (SELECT MAX(Rotate)+1 FROM Table1 WHERE Vol=1)
ELSE MIN(o.Rotate) END as nRotate FROM Table1 o INNER JOIN Table2 s ON o.SID=s.ID 
WHERE s.Date >='7/30/2004' And s.ID<>100 And o.Vol=1 and 
Case s.DATE 
When '7/30/2004' then s.Sales>'Doe, Jane'
End

Upvotes: 0

Views: 94

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You don't need case:

WHERE s.Date >='2004-07-30' And s.ID <> 100 And o.Vol = 1 and 
      (s.date <> '2004-07-30' or s.Sales > 'Doe, Jane')

Upvotes: 1

Related Questions