Conditional SQL Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Conditional SQL Query

Post 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?
Last edited by Benjamin on Tue Mar 21, 2006 12:46 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

... (`ChairType` = 1 AND `ChairAge` != 2) ...
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

:D

You beat me to it.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you'll need more than just that snip, but since you didn't specify anything else, it's kinda hard to help..
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Which DB server and version are you on?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I'm not sure my client is not available right now I believe it is the most recent version.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

there's a simple way.. keep screwing around with it.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Code: Select all

IF(`ChairType` = '1',`ChairAge` < '2',1) AND
WORKS! :lol:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Congrats!
Post Reply