3

Strange problem.

My Query looks like

SELECT DISTINCT ID, `etcetc`, `if/elses over muliple joined tables` FROM
    table1 AS `t1`
    # some joins, eventually unrelated in that context
WHERE
# some standard where statements, they work/

CASE 
    WHEN `t1`.`field` = "foo" THEN (`t1`.`anOtherField` != 123 AND `t1`.`anOtherField` != 456 AND `t1`.`anOtherOtherField` != "some String")
    WHEN `t1`.`field` = "bar" THEN `t1`.`aSecondOtherField` != 12345
    END

#ORDER BY CASE etc. Standard Stuff

Apperantly MySQL returns a wrong rowcount and I think my problem is in the logic of the WHERE ... CASE statement. Maybe with the brackets? Maybe I should go for operator OR and not AND? Should my the second WHEN include brackets also, even when I only compare one field? Should I use IF and not CASE?

Basically I want to exclude some rows with specific values IF theres a specific value in field foo or bar

I would try that all out, but it takes a huge amount of time to complete that query... :(

Edit: Just for the notes, my problem was, that I forgot the ELSE in my CASE.

CASE 
    WHEN `t1`.`field` = "foo" THEN (`t1`.`anOtherField` != 123 AND `t1`.`anOtherField` != 456 AND `t1`.`anOtherOtherField` != "some String")
    WHEN `t1`.`field` = "bar" THEN (`t1`.`aSecondOtherField` != 12345)
    ELSE TRUE
END

This solution works also, but the posted one is better...

handfix
  • 134
  • 1
  • 1
  • 6
  • This one is not the same as the solution below. ELSE TRUE -> OR (t1.field not in ('foo', 'bar')) – newtover Apr 15 '10 at 09:58
  • Yes, I know. I forgot to mention, that `t1`.`field` either can be an empty string (in that case no row should be returned) or some other string than 'foo' or 'bar' (in that case the row *should* be returned (+ some other field should be modified, its a complex qry)). Therefore I modified your solution to fit my needs. – handfix Apr 15 '10 at 10:30

1 Answers1

6

You should use OR here instead of CASE:

WHERE
(`t1`.`field` = "foo" AND `t1`.`anOtherField` != 123 AND `t1`.`anOtherField` != 456 AND `t1`.`anOtherOtherField` != "some String")
OR
(`t1`.`field` = "bar" AND `t1`.`aSecondOtherField` != 12345)
newtover
  • 31,286
  • 11
  • 84
  • 89
  • Oh god, your right, im such a douchebag. Haha. I clearly overdesigned that one, I was sure that it just could not be THAT easy and therefore didnt even thought about that solution entirely. Maybe happens when you write a page-long query. – handfix Apr 15 '10 at 09:43