Return multiple rows if condition satisfies for atleast one row

Question

I am new to SSMS and I have a feeling this should be easy but I have no clue on how to do it. For the sake of simplicity, I renamed my tables.

SKU Reason
s1  r1
s1  r2
s1  r3
s2  r1
s2  r3
s3  r5
s3  r1
s3  r4

To give a context, SKU 's1' is excluded because of three reasons: r1, r2, r3. s2 is excluded because of 2 reasons r1 and r3. s3 is excluded because of 3 reasons r5,r1, and r4.

I can filter the application based on SKU or Reason or both. If I filter based on reason r1, it should return me all SKUs that are excluded because of r1 i.e s1, s2, s3 in the above example. If I filter based on reason r1 and SKU s1, its currently returning me s1 and r1 in the result which is what I wanted.

Now, the requirement has changed. If I filter based on reason r1 for SKU s1, it should not only return me s1 and r1 but also other reasons because of which sku s1 is excluded. i.e it should return s1 r1, s1 r2, s1 r3. However, if I filter by reason r5 for SKU s1, it should return nothing as SKU s1 is not excluded by reason r5.

Current Implementation:

Filter Criteria: Sku s1 and Reason R1

SKU Reason
s1   r1

I want to change it to:

Filter Criteria: Sku s1 and Reason R1

SKU Reason
s1   r1
s1   r2
s1   r3

score 1 · Answer 1 · answered Mar 28 '18 at 19:38

Here's a complete solution with select statements to try out all the different conditions:

declare @t table (sku varchar(2), reason varchar(2))
insert into @t values ('s1', 'r1')
insert into @t values ('s1', 'r2')
insert into @t values ('s1', 'r3')
insert into @t values ('s2', 'r1')
insert into @t values ('s2', 'r3')
insert into @t values ('s3', 'r5')
insert into @t values ('s3', 'r1')
insert into @t values ('s3', 'r4')

declare @sku_filter varchar(2)
declare @reason_filter varchar(2)
-- try each of these scenarios
select @sku_filter = 's1', @reason_filter = 'r1'
--select @sku_filter = 's1', @reason_filter = 'r5'
--select @sku_filter = null, @reason_filter = 'r1'
--select @sku_filter = 's1', @reason_filter = null
--select @sku_filter = null, @reason_filter = null

select sku, reason
from @t t
where (@sku_filter is not null and @reason_filter is not null and t.sku = @sku_filter and exists (select * from @t t where sku = @sku_filter and reason = @reason_filter))
or (@sku_filter is not null and @reason_filter is null and t.sku = @sku_filter)
or (@sku_filter is null and @reason_filter is not null and t.reason = @reason_filter)
or (@sku_filter is null and @reason_filter is null)

I find it a lot easier to read these when the conditions are grouped together in parentheses (e.g. @param1 is not null and @param2 is null).

I see what you are trying to do here. However, my sku_filter and reason_filter take a list of values and not a single value. How does my query change when I check for null values? Right now, it looks like sku_filter IN (NULL) and I am not sure if its the right way to check for NULL in list of values — user1982519, Mar 29 '18 at 15:36

score 0 · Answer 2 · answered Mar 28 '18 at 19:30

0

select * from table where sku in (select sku from table where sku='s1' and reason='r1')

answered Mar 28 '18 at 19:30

Krishna

529
3
13

score 0 · Answer 3 · answered Mar 28 '18 at 19:30

0

I think this form of filter you want to build:

    declare @sku varchar(50) = 's1'
    declare @reason varchar(50) = 'r1'

    SELECT * FROM TAB1 where (SKU=@sku ) 
    and exists (
    SELECT * FROM TAB1 where SKU=@sku AND Reason = @reason
    )

answered Mar 28 '18 at 19:30

Alexander Volok

5,630
3
17
33

Return multiple rows if condition satisfies for atleast one row

3 Answers3