I am trying to figure out how to get a query to come back with certain rows if other rows contain certain data.

SKU	Bin
1234	A1
1234	NoBin
4567	NoBin
8997	Conveyor
6543	MLSTAGE
2101	A2
2101	Conveyor

This is a very simple version but I only want to see SKUs 4567, 8997, and 6543 come back because 1234 and 2101 are located in Bin A1 and A2. I only want to see items that are ONLY in NoBin, MLSTAGE, or Conveyor.

I can't figure out how to get it and of course there are millions of SKUs.

Any help would be great. Thank you

CodePudding user response：

select SKU
from T
group by SKU
having count(case when Bin not in ('NoBin', 'MLSTAGE', 'Conveyor') then 1 end) = 0

or equivalent (if there are nulls):

having count(case when Bin in ('NoBin', 'MLSTAGE', 'Conveyor') then 1 end) = count(*)

CodePudding user response：

You essentially want to check for existence, so EXISTS seems a logical choice:

SELECT  *
FROM    YourTable T
WHERE   NOT EXISTS (
                       SELECT NULL
                       FROM   YourTable T2
                       WHERE  T2.Bin NOT IN ('NoBin', 'MLSTAGE', 'Conveyor')
                          AND T2.SKU = T.SKU
                   )
    AND T.Bin IN ('NoBin', 'MLSTAGE', 'Conveyor') 

CodePudding user response：

you could use a not exists

select * from mytable a
 where not exists (
   select 1 from mytable b
   where a.sku = b.sku and b.bin not in
   ('NoBin', 'MLSTAGE', 'Conveyor'))

here is the fiddle https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5c370c7766acfbb9552bbbd32cf5e03a

CodePudding user response：

SELECT 
SKU 
FROM [SKU TEST]
WHERE SKU NOT IN 
(
SELECT SKU
FROM [SKU TEST]
WHERE BIN NOT IN ('NoBin', 'Conveyor', 'MLSTAGE')
)

Inner query is used to build a set of SKU that are associated with everything you don't want, so not in your 3 criteria. The outer query is generating a list of SKU's that are not in your inner set which should give you want you want. I went with basic script to ensure it would work with pretty much any RDBMS.