CODEWITHSUNDEEP
sqloracle-database
Jiman
Jiman

Reputation: 185

Select objects with child objects that have both null and non-null attributes

Say I have a table that links a parent object with multiple child objects and has columns for certain attributes of the child object. Take this simplified example below:

product_id  sku_id  size_desc  color_column_that_doesnt_matter
123         456     (null)     black
123         457     (null)     red
123         458     (null)     grey
124         567     large      green
124         568     medium     green
124         569     small      green
125         678     (null)     blue
125         679     5x5        yellow

Notice that Product 123 and 124 have products that all have either a null for the size description or a value for the size description. Product 125 is different though - It has at least one sku w/ a null size_desc and at least one one sku that has a non-null value for size_desc.

Say there are potentially very many products like Product 125 - I want them all. How would I filter this data via a select…where statement to return all products and all skus that have this mix of null and non-null values for size_desc?

SQL flavor is Oracle 11

Upvotes: 0

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

You can get the products like this:

select product_id
from table t
group by product_id
having count(*) <> count(size_desc) and count(size_desc) > 0;

You can get the detail for all these products by switching this to analytic functions:

select t.*
from (select t.*, count(*) over (partition by product) as cnt,
             count(size_desc) over (partition by product) as cnt_sizedesc
      from t
     ) t
where cnt <> cnt_sizedesc and cnt_sizedesc > 0;

Upvotes: 1

Related Questions