CODEWITHSUNDEEP
sql-serverssmsssms-16
user1982519
user1982519

Reputation: 515

Return multiple rows if condition satisfies for atleast one row

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

Upvotes: 1

Views: 746

Answers (3)

Max Szczurek
Max Szczurek

Reputation: 4334

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).

Upvotes: 1

Alexander Volok
Alexander Volok

Reputation: 5940

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
    )

Upvotes: 0

Krishna
Krishna

Reputation: 549

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

Upvotes: 0

Related Questions