# SQL. Multiple values for the condition.
**URL:** https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927
**Category:** Factual Questions
**Created:** [June 14, 2008, 5:14pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927 "2008-06-14T17:14:08Z")
**Posts on this page:** 15
**Page:** 1
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [June 14, 2008, 5:14pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/1 "2008-06-14T17:14:08Z")
I am trying to pick one output if the input is one of several string values.
For example (using foodstuff as an example) …
if the foodstuff is one of these - banana, strawberry, apple, orange then output ‘fruit’ else output ‘not fruit’
I can’t seem to find any online case help that explains how to construct the code when you have multiple condition values.
The action to be performed when the condition is one of the values is quite large, so I don’t want to have to repeat it for every individual item
---
### Author:  [@Do\_Not\_Taunt](https://boards.straightdope.com/u/Do_Not_Taunt)
#### Post date: [June 14, 2008, 5:43pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/2 "2008-06-14T17:43:24Z")
[QUOTE=Lobsang]
I am trying to pick one output if the input is one of several string values.
For example (using foodstuff as an example) ..
if the foodstuff is one of these - banana, strawberry, apple, orange then output ‘fruit’ else output ‘not fruit’
I can’t seem to find any online case help that explains how to construct the code when you have multiple condition values.
The action to be performed when the condition is one of the values is quite large, so I don’t want to have to repeat it for every individual item
[/QUOTE]
You mean in the context of a stored procedure? The following would work in mySql:
if (foodstuff like ‘banana’ || foodstuff like ‘strawberry’ || foodstuff like ‘apple’) then
– blah blah blah
end if;
_or_
if foodstuff in (‘banana’, ‘strawberry’, ‘apple’) then
– blah blah blah
end if;
The advantage of the former way is you can use like with wildcard matches. AFAIK in is restricted to equality testing.
Is that what you’re looking for?
---
### Author:  [@Sparklo](https://boards.straightdope.com/u/Sparklo)
#### Post date: [June 14, 2008, 5:46pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/3 "2008-06-14T17:46:52Z")
Depending on what SQL implementation you’re using, there may be a function that can help. For example, in MySQL, you could write it as
SELECT IF(FIND\_IN\_SET(fruit\_name, ‘banana,apple,orange’) \> 0, ‘fruit’, ‘not fruit’)
FROM …
WHERE …
Another thought: you could have a separate table with entries for each fruit. Then, you can join your fruit\_name column to the separate table to get the group. You could have entries for everything (all fruits and all non-fruits) or just entries for fruits and use a left join (if you get a null back, you know it’s not a fruit).
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [June 14, 2008, 6:17pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/4 "2008-06-14T18:17:58Z")
The replies use alternatives to the ‘case’ statement. Is there a way of doing it within a case statement? (in c++ it would be a ‘fall-through’ to one action for many conditions)
Here’s what I’ve had to do in the meantime…
-
((case pool\_type
when ‘WN’ then (select commission from amtotecommissions where program\_name=a.program\_name and pool\_type=‘WN’ and \_date=a.\_date)
when ‘PL’ then (select commission from amtotecommissions where program\_name=a.program\_name and pool\_type=‘WN’ and \_date=a.\_date)
when ‘SH’ then (select commission from amtotecommissions where program\_name=a.program\_name and pool\_type=‘WN’ and \_date=a.\_date)
when ‘WP’ then (select commission from amtotecommissions where program\_name=a.program\_name and pool\_type=‘WN’ and \_date=a.\_date)
when ‘PS’ then (select commission from amtotecommissions where program\_name=a.program\_name and pool\_type=‘WN’ and \_date=a.\_date)
when ‘WS’ then (select commission from amtotecommissions where program\_name=a.program\_name and pool\_type=‘WN’ and \_date=a.\_date)
when ‘WPS’ then (select commission from amtotecommissions where program\_name=a.program\_name and pool\_type=‘WN’ and \_date=a.\_date)
else
(select commission from amtotecommissions where program\_name=a.program\_name and pool\_type=a.pool\_type and \_date=a.\_date)
-
You can see that the action for WN,PL,SH,WP,PS,WS,WPS, is exactly the same each time.
The table structure is fixed. I can’t change it.
---
### Author:  [@chrisk](https://boards.straightdope.com/u/chrisk)
#### Post date: [June 14, 2008, 6:59pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/5 "2008-06-14T18:59:39Z")
[QUOTE=Lobsang]
The replies use alternatives to the ‘case’ statement. Is there a way of doing it within a case statement? (in c++ it would be a ‘fall-through’ to one action for many conditions)
Here’s what I’ve had to do in the meantime…
You can see that the action for WN,PL,SH,WP,PS,WS,WPS, is exactly the same each time.
The table structure is fixed. I can’t change it.
[/QUOTE]
Okay, I’d probably try a searched case, where you leave out the input expression but can put any complex expressions into the when spot, like:
```auto
case pool_type
when pool_type in ('WN', 'PL', 'SH', 'WP', 'PS', 'WS', 'WPS') then
(select commission from amtotecommissions
where program_name=a.program_name and pool_type='WN' and _date=a._date)
else
(select commission from amtotecommissions
where program_name=a.program_name and pool_type=a.pool_type and _date=a._date)
```
Does that work?
The other option would be writing a database function, if you can do that in the database.
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [June 14, 2008, 7:07pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/6 "2008-06-14T19:07:45Z")
[QUOTE=chrisk]
Okay, I’d probably try a searched case, where you leave out the input expression but can put any complex expressions into the when spot, like:
```auto
case pool_type
when pool_type in ('WN', 'PL', 'SH', 'WP', 'PS', 'WS', 'WPS') then
(select commission from amtotecommissions
where program_name=a.program_name and pool_type='WN' and _date=a._date)
else
(select commission from amtotecommissions
where program_name=a.program_name and pool_type=a.pool_type and _date=a._date)
```
Does that work?
The other option would be writing a database function, if you can do that in the database.
[/QUOTE]
I did try that (it was my first thought - I’ve used the very convenient ‘in’ feature in other queries) but it didn’t work - complained about ‘in’ as a sytax errror.
I can write functions. I would have thought that something as powerful as SQL would allow this quite basic and probably common feature in it’s case construct.
Anyway… having now written the code with the repeated action I am not in need of an answer - but it would be useful to know so that I can make my sql queries look less like traditional ten million line programming language code!
edit:hang on a minute…
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [June 14, 2008, 7:15pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/7 "2008-06-14T19:15:45Z")
babapoopoo - I missed the edit window…
It does work afterall… Just have to modify the case syntax …
instead of \* case [field] when [value]\* (mine) or _case [field] when [field] in ([values])_ you use _case when [field] in ([values])_
My excuse is that the ‘standard’ of constructing the case statement is inconsistent between the two ways- (between ‘when the value is these do this’ and ‘when the value is this do this’)
so, thanks **chrisk** for showing me how to do it 🙂
If only there were a way of returning the favour to all the people I’ve leeched SQL help from! 😃
---
### Author:  [@chrisk](https://boards.straightdope.com/u/chrisk)
#### Post date: [June 14, 2008, 7:23pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/8 "2008-06-14T19:23:34Z")
[QUOTE=Lobsang]
babapoopoo - I missed the edit window…
It does work afterall… Just have to modify the case syntax …
instead of \* case [field] when [value]\* (mine) or _case [field] when [field] in ([values])_ you use _case when [field] in ([values])_
My excuse is that the ‘standard’ of constructing the case statement is inconsistent between the two ways- (between ‘when the value is these do this’ and ‘when the value is this do this’)
so, thanks **chrisk** for showing me how to do it 🙂
If only there were a way of returning the favour to all the people I’ve leeched SQL help from! 😃
[/QUOTE]
Actually, it’s not really ‘when the value is these do this’ (or ‘when the value is these return this’)
It’s “when this is true then return this”
Which has the advantage of giving you all kinds of possible things that you can test, as long as they come down to a true or false - letting you use complicated tests that involve three different columns, as well as ‘is this value one of these’. But the syntax is indeed different and a bit harder to get your head around. Even I have to check the SQL server manual sometimes.
I’ve developed a bit of a reputation at work, I think, for doing clever and tricky things with case statements - like group by case, order by case, and join on fieldname = case. 😉
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [June 14, 2008, 7:28pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/9 "2008-06-14T19:28:35Z")
**chrisk** I think I love you - You may have solved another problem I haven’t even posted about, with your last sentence - ‘Group By case’.
I am wondering if I can do the same thing with a sum, when the pool\_type is ‘WN’,'PL,‘SH’,‘WP’,‘PS’,‘WS’ combine into ‘WPS’ else group by what’s left.
Off to try it I go…
[sub]p.s. If you feel a little uncomfortable - I don’t _really_ love you, but I might owe you a pint![/sub]
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [June 14, 2008, 7:39pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/10 "2008-06-14T19:39:37Z")
So you might be able to help me…
how would I go about grouping all the variations of ‘win’ ‘place’ and ‘show’ into one WPS group?
(I’ve tried unsuccesfully)
---
### Author:  [@chrisk](https://boards.straightdope.com/u/chrisk)
#### Post date: [June 14, 2008, 7:55pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/11 "2008-06-14T19:55:51Z")
[QUOTE=Lobsang]
So you might be able to help me…
how would I go about grouping all the variations of ‘win’ ‘place’ and ‘show’ into one WPS group?
(I’ve tried unsuccesfully)
[/QUOTE]
Hmm… I’m not sure about what your table is like, but the basics would look like:
select case when result in (‘win’, ‘place’, ‘show’) then ‘win-place-show’
else ‘you suck!’ end label, count(\*)
from race\_horses
group by case when result in (‘win’, ‘place’, ‘show’) then ‘win-place-show’
else ‘you suck!’ end
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [June 14, 2008, 8:09pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/12 "2008-06-14T20:09:36Z")
There is smoke coming out of my ears.
In your example ‘you suck’ needs to be a field rather than a string. so "group by ‘WPS’ when the value is (WN PL SH etcc) or by pool\_type
so I could end up with a table like this…
WPS (value)
EX (value)
QN (value)
TR (value)
instead of
WN (value)
PL (value)
SH (value)
WP (value)
PS (value)
WS (value)
WPS (value)
EX (value)
QN (value)
TR (value)
But it doesn’t work (I get the complaint - ‘pool type’ is invalid in the select list because it is not contained in an agregate function or group by clause (even though it is!))
so for now - I’ve solved it temporarily by wrapping the whole mega query inside another query with a simple group by clause (in other words crate a table that shows ‘WPS’ for all the variants and put it in a new field) then run a query on _that_ grouping by the new field…
select site,program\_name,pool\_type,sum(handle) as handle,sum(commission) as commission from
(select (case trackingid when 2 then ‘site1’ else ‘site2’ end) as site,
program\_name,
(case when pool\_type in (‘WN’,‘PL’,‘SH’,‘WP’,‘PS’,‘WS’,‘WPS’) then ‘WPS’
else pool\_type end) pool\_type,
sum(cast(total\_bet\_amount as real)-cast(refund\_amt as real)) as handle,
((case when pool\_type in (‘WN’,‘PL’,‘SH’,‘WP’,‘PS’,‘WS’,‘WPS’) then (select commission from amtotecommissions where program\_name=a.program\_name and pool\_type=‘WN’ and \_date=a.\_date)
else
(select commission from amtotecommissions where program\_name=a.program\_name and pool\_type=a.pool\_type and \_date=a.\_date)
end
)/100)\* sum(cast(total\_bet\_amount as real)-cast(refund\_amt as real)) as commission
from amtoteaccountactivity a inner join clients on a.accountnumber = clients.accountnumber where \_date=‘08/06/11’ and transaction\_type = ‘Bet’
group by \_date,program\_name,trackingid,pool\_type) q1
group by program\_name,site,pool\_type
order by program\_name,site,pool\_type
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [June 14, 2008, 8:22pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/13 "2008-06-14T20:22:13Z")
I have been cursed with being the SQL man for the worst designed database on earth.
---
### Author:  [@chrisk](https://boards.straightdope.com/u/chrisk)
#### Post date: [June 14, 2008, 8:31pm UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/14 "2008-06-14T20:31:17Z")
[QUOTE=Lobsang]
But it doesn’t work (I get the complaint - ‘pool type’ is invalid in the select list because it is not contained in an agregate function or group by clause (even though it is!))
[/QUOTE]
Hmm… the tricky thing about group by case is that the case statement in the select has to be EXACTLY the same as the one that you’re grouping by - well, differences in white space are the same, but it has to parse exactly the same way. If you do that right, then you shouldn’t get that error message. ‘Pool type’ is not actually in the group by clause by itself, but you can get around that by grouping on the complex expression.
And - condolences for having to work with a badly designed database. It really can make certain things about 10 thousand times harder to do. ☹
---
### Author:  [@Quartz](https://boards.straightdope.com/u/Quartz)
#### Post date: [June 15, 2008, 8:40am UTC](https://boards.straightdope.com/t/sql-multiple-values-for-the-condition/452927/15 "2008-06-15T08:40:51Z")
I’ve seen you ask a lot of SQL questions recently: perhaps you should suggest to your boss that you go on a SQL course?