Select records with CASE statement

Transact-SQL
1

I have a table like this

 Table1
 Items	       Qty
 Item A    	1
 Item B    	5
 Item C    	3
 Item D    	7
 Item E    	1
 Item F    	2
 Item G         10
 Item H    	1
 Item I     	8

I need to write a statement like:

Declare @selection int = (can be 0, 1, or 2)
Select Items, Qty From Table1
Where
CASE
-- when @selection = 0, select all items
-- when @selection = 1, select items that have qty = 1
-- when @selection = 2, select items that have qty > 1
END

How do I implement my CASE statement? Thanks

2

put a where clause:

where @selection = 0
or @selection = 1 and qty = 1

etc

3

This code works for me:

Where @selection = 0
OR (@selection = 1 and qty = 1)
OR (@selection = 2 and qty > 1)

Thanks for the advise

4

You might want to get in the habit of putting parenthesis around the "OR" section so you do not forget if there is an "AND".

5

... and leaving them off things that are ANDed together, since precedence takes care of it for you

6

"I'd like a burger and fries or hash-browns"

...

"How come you just got me hash-browns?" :frowning:

7

cause you gave me a choice?

Note that to my point, this is the same as "I'd like a (bugger [sic] and fries) or hash-browns

8

Sure, but as a human you knew what I meant, even though it was ambiguous and thus not necessarily conforming to precedence rules :slight_smile:

Well I hope you did ... if you just bring me Hash Browns I'll then realise that you didn't!

9

Of course, most waiters would confirm the order. If given a choice, they'd simply bring the highest-priced combo. Now what if I asked for "fries and burger or hash browns" ? even more ambiguous. we tend to group things according to categories (fries and hash browns are both potatoes) and good AI can do the same.

Anyway I advocate removing parentheses around ANDed items. Since (burger and fries) means the same as "burger and fries" both to you and SQL (precedence). However, what I suppose you wanted is "burger and (fries or hash browns)" and you certainly do need the parentheses around the ORed items. Both for people and machines!

10

Ah ... but that would be inversely proportional to their tip, no?!!

11

no. the bigger the bill, the bigger the tip, right?

12

Only if I don't spot that I've been shafted ...