Page 1 of 1
Conditional SQL Query
Posted: Tue Mar 21, 2006 12:41 am
by Benjamin
I'm trying to write a query which excludes certain records conditionally depending on the value of another field in the same table. The logic would be as follows...
If ChairType is 1
Exclude all records where the ChairAge = 2
Code: Select all
WHERE (`ChairAge` != '2' WHEN `ChairType` = '1') AND blah=blah AND blah=blah etc. etc...
This is just a portion of a very long query. I took a stab at using the WHEN clause with parantheses but it doesn't work. Any ideas? Couldn't find anything in the manual.
EDIT:
I changed it to,
Code: Select all
WHERE (`ChairAge` != '2' AND `ChairType` = '1') AND blah=blah AND blah=blah etc. etc...
And that appears to work, I'll have to check the results and make sure they are correct. Is there another way to do this?
Posted: Tue Mar 21, 2006 12:45 am
by feyd
Code: Select all
... (`ChairType` = 1 AND `ChairAge` != 2) ...
Posted: Tue Mar 21, 2006 12:47 am
by Benjamin
You beat me to it.
Posted: Tue Mar 21, 2006 12:52 am
by Benjamin
I checked the results and the query won't work because it's excluding results where the ChairType isn't equal to 1. That is why I need to use something like a WHEN clause. Is there a way to make this work with a query?
Code: Select all
HERE (`ChairAge` != '2' WHEN `ChairType` = '1') AND blah=blah AND blah=blah etc. etc...
Posted: Tue Mar 21, 2006 12:57 am
by feyd
you'll need more than just that snip, but since you didn't specify anything else, it's kinda hard to help..
Posted: Tue Mar 21, 2006 12:59 am
by RobertGonzalez
Which DB server and version are you on?
Posted: Tue Mar 21, 2006 12:59 am
by Benjamin
I just need to know how to do a conditional mysql query or if is even possible. I haven't found anything useable in the manual.
Posted: Tue Mar 21, 2006 1:01 am
by Benjamin
I'm not sure my client is not available right now I believe it is the most recent version.
Posted: Tue Mar 21, 2006 1:03 am
by RobertGonzalez
Certain versions allow user functions. MySQL 5.0+ allows stored proc's, which would fit this scenario really well. That is why I ask.
In my experience queries like this don't return the expected (or desired) results in MySQL 4.0 and below.
Posted: Tue Mar 21, 2006 1:11 am
by Benjamin
Ok, I'm amazed there isn't a simple way to do this. I thought for sure something like WHEN or IF would work.
Posted: Tue Mar 21, 2006 1:18 am
by feyd
there's a simple way.. keep screwing around with it.
Posted: Tue Mar 21, 2006 1:29 am
by Benjamin
Well this is what I tried so far, sorry I can't post more of the query.
(each line is an attempt)
Code: Select all
select blah CASE `ChairType` WHEN '1' THEN `ChairAge` < '2' ELSE END CASE AND blah
select blah (CASE `ChairType` WHEN '1' THEN `ChairAge` < '2' ELSE END CASE) AND blah
select blah (CASE `ChairType` WHEN '1' THEN `ChairAge` < '2' ELSE CASE) AND blah
select blah (CASE `ChairType` WHEN '1' THEN `ChairAge` < '2' ELSE CASE;) AND blah
select blah (`ChairAge` < '2' IF(`ChairType` = '1')) AND blah
select blah (`ChairAge` < '2' IF `ChairType` = '1') AND blah
select blah (`ChairAge` < '2' WHEN `ChairType` = '1') AND blah
select blah (`ChairAge` < '2' WHERE `ChairType` = '1') AND blah
Posted: Tue Mar 21, 2006 1:48 am
by Benjamin
Code: Select all
IF(`ChairType` = '1',`ChairAge` < '2',1) AND
WORKS!

Posted: Tue Mar 21, 2006 8:20 am
by RobertGonzalez
Congrats!