CODEWITHSUNDEEP
sqlreporting-services
JeffreyLazo
JeffreyLazo

Reputation: 853

MS SQL/SSRS Query Parameter

This is going to be a simple one, I just can't figure it out.

select * from table

Returns:

|Item|Ordernumber|Color
|apple|2|Green|
|orange|2|yellow|
|apple|3|Red|

I want to filter on color

select * from table 
where color = Green

I get, as expected

|Item|Ordernumber|Color
|apple|2|Green|

I would like to show all items for the same order number IF the order contains any items with color Green

|Item|Ordernumber|Color
|apple|2|Green|
|orange|2|yellow|

I am creating a parameter in SSRS.

Upvotes: 0

Views: 32

Answers (2)

vinod meena
vinod meena

Reputation: 63

You have to create a parameter Color and put the parameter filter instead of hardcoding the color in the below code:- 

select item, ordernumber, color 
from table where ordernumber in 
(select distinct ordernumber from table 
 where color = 'Green')

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can first get the order number from the inner query (where color is green) and join it to the original table.

select item, t.ordernumber, color 
from table t join
(select ordernumber from table 
where color = 'Green') x
on x.ordernumber = t.ordernumber

Upvotes: 1

Related Questions