CODEWITHSUNDEEP
sqlsql-server
Y. Style
Y. Style

Reputation: 13

How to write this query in SQL Server 2008?

I have a table [dbo].[product]:

[id] [component] [quantity]  
01----------A-------------2  
02----------A-------------6  
03----------A-------------8  
04----------B-------------1  
05----------B-------------2  
06----------C-------------4  
07----------C-------------7  
08----------C-------------5  
09----------C-------------9  
10----------C-------------3  
11----------D-------------2  
12----------D-------------7

And I need a query to find these records:

  1. component in ('B','C','D') AND
  2. Quantity of B > 1 AND
  3. Quantity of C > 5 AND
  4. Quantity of D > 6

Results should be: 

[id] [component] [quantity]   
05----------B-------------2  
07----------C-------------7  
09----------C-------------9  
12----------D-------------7

Upvotes: 1

Views: 38

Answers (2)

Raj Kamuni
Raj Kamuni

Reputation: 388

SELECT * FROM Table_NAME
WHERE component  IN ('B','C','D')  
AND Quantity >(
(CASE WHEN (component='B') THEN 1
      WHEN (component='C') THEN 5
      ELSE 6
      END)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can just do this with an and and or:

where (component = 'B' and quantity > 1) or
      (component = 'C' and quantity > 5) or
      (component = 'D' and quantity > 6)

Upvotes: 3

Related Questions