Click here to Skip to main content
15,941,973 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Guys,
Need to get price from multiple queries.

if 1st query price is 0 then execute 2nd query.
if 2nd query price is 0 then execute 3rd query.

I tried with case statement. but it's not working as expected.

Please help me to fix this.

What I have tried:

select R.Id,(case 
when R.Price !=0.00 then R.Price
when R.Price =0.00
(select max(C.price) from ItemOption IO, Options O, ItemOptionChoices C where IO.OptionId=O.OptionId 
and O.OptionId = C.OptionId and O.MultipleSelections='Radio Buttons' and MenuItemId=R.Id)
 when R.Price =0.00
 (select max(price) as variantprice from ItemVariation IV where  IV.ItemId=R.Id)
 end) as Price
from RestaurantMenu as R where CategoryId='5b7a3bfd-427f-4813-a54e-ff32f47b95a4' and EntityType='Item'
 Order by CategoryOrder
Updated 29-Nov-18 23:47pm
OriginalGriff 30-Nov-18 4:35am    
"It doesn't work" is probably the most useless problem report we get - and we get it a lot. It tells us nothing about what is happening, or when it happens.
So tell us what it is doing that you didn't expect, or not doing that you did.
Tell us what you did to get it to happen.
Tell us any error messages.
Use the "Improve question" widget to edit your question and provide better information.
CHill60 30-Nov-18 4:38am    
While you are editing your question why not throw in some sample data and expected results!

1 solution

Rather than using those sub-queries try using Common Table Expressions.
Note that you have listed the condition WHEN R.Price =0.00 twice.
That is a really, really old fashioned way of expressing joins - don't do it that way.

From the little information you have given us the following query might work.
;with c1 as
	select max(C.price) as C1Price, MenuItemId
	from ItemOption IO, 
	join Options O ON IO.OptionId=O.OptionId
	join ItemOptionChoices C ON  O.OptionId = C.OptionId
	where O.MultipleSelections='Radio Buttons' 
), c2 as 
	select max(price) as c2Price, ItemId as variantprice from ItemVariation IV 
select R.Id,
	(SELECT TOP 1 P FROM (VALUES (R.Price),(C1Price),(C2Price)) as tmp(P) WHERE P <> 0 ORDER BY P ASC) as maxPrice
from RestaurantMenu as R
left join c1 ON MenuItemId=R.Id
left join C2 ON IV.ItemId=R.Id
where CategoryId='5b7a3bfd-427f-4813-a54e-ff32f47b95a4' and EntityType='Item'
 Order by CategoryOrder
Note the use of an in-line temporary table to get the values - adapted from a solution by sven[^]

This may some extra work to handle nulls.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900