SQLTeam.com | Weblogs | Forums

Selecting records with common row values based on another column

#1

I am trying to use SKU to filter down to a result that say SKU 2 and 5 are inactive across warehouses. I have database with a tremendous amount of records and my ultimate goal is is to determine the SKU (Stock Keeping Units) items that are not active across any warehouse. All Locations have to be Active(No) to be included in the result set. (Active is a Binary field) I have tried a bunch of ideas buy am coming up short on this one. Thank you for some guidance on this one.

SKU, WAREHOUSE, Active
1 New York 1
1 Chicago 1
1 Boston 0

2 New York 0
2 Chicago 0
2 Boston 0

3 New York 0
3 Chicago 1
3 Boston 0

4 New York 1
4 Chicago 1
4 Boston 1

5 New York 0
5 Chicago 0
5 Boston 0

End Result Desired
SKU
2
5

#2

Suggestion 1:

select sku
  from yourtable as a
 where not exists(select 1
                    from yourtable as b
                   where b.sku=a.sku
                     and b.active<>0
                 )
 group by sku
;

Suggestion 2:

select sku
  from yourtable
 group by sku
 having sum(cast(active as int))=0
;
#3

hi

Please see another way of doing this ..

Hope it helps

:slight_smile:
:slight_smile:

drop create data .. 
use tempdb 
go 

drop table data 
go 

create table data
(
SKU int ,
WAREHOUSE varchar(100),
Active int 
)

insert into data select 1 ,'New York ',1
insert into data select 1 ,'Chicago ',1
insert into data select 1 ,'Boston ',0

insert into data select 2 ,'New York ',0
insert into data select 2 ,'Chicago ',0
insert into data select 2 ,'Boston ',0

insert into data select 3 ,'New York ',0
insert into data select 3 ,'Chicago ',1
insert into data select 3 ,'Boston ',0

insert into data select 4 ,'New York ',1
insert into data select 4 ,'Chicago ',1
insert into data select 4 ,'Boston ',1

insert into data select 5 ,'New York ',0
insert into data select 5 ,'Chicago ',0
insert into data select 5 ,'Boston ',0
go
SQL ... 
;WITH cte 
     AS (SELECT sku, 
                Max(active) AS maxc 
         FROM   data 
         GROUP  BY sku) 
SELECT * 
FROM   cte 
WHERE  maxc = 0 

go

image

#4

Thank you very much for taking the time to thoroughly answer my SQL question. Your SQL skills are exceptional. Again thank you very much - greatly appreciated !!! . I assume others down the road will also benefit from your answers.

#5

hi david

please dont mind my asking

Thank you very much for taking the time to thoroughly answer my SQL question. Your SQL skills are exceptional. Again thank you very much - greatly appreciated !!! . I assume others down the road will also benefit from your answers.

who was this directed to ?
thanks