2

Based on SQL Server, I have multiple rows with multiple columns. Each row contains a product-configuration which is different in at least one column. Now I need to know which are the not existing configurations. The three columns are only an example to make it easier to understand.

An example of data:

Product Size Color
Shirt S Red
Shirt S Blue
Shirt S Black
Shirt M Red
Shirt M Blue
Shirt L Yellow

As a result, those configurations are missing:

Product Size Color
Shirt S Yellow
Shirt M Black
Shirt M Yellow
Shirt L Red
Shirt L Blue
Shirt L Black
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Fantasterei
  • 465
  • 3
  • 11

2 Answers2

1

You can use cross join to generate all the rows. Then filter out the ones that exist:

select p.product, s.size, c.color
from (select distinct product from t) p cross join
     (select distinct size from t) s cross join
     (select distinct color from t) c left join
     t
     on p.product = t.product and
        s.size = t.size and
        c.color = t.color
where t.product is null;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Gordon's answer doesn't take into account nullable columns, here is one that does:

select p.product, s.size, c.color
from (select distinct product from t) p cross join
     (select distinct size from t) s cross join
     (select distinct color from t) c
except
select t.product, t.size, t.color
from t;

If you have actual product, size and color tables, it will be more performant to use those instead of select distinct... from t

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • FWIW: the answer from Gordon Linoff is standard SQL that is widely supported. `EXCEPT` is not supported by all DMBS. – Kaii Feb 17 '21 at 17:56
  • Also i would assume `product` can not be NULL, so it really doesn't make any difference. – Kaii Feb 17 '21 at 18:00
  • @Kaii I don't believe that is true see https://stackoverflow.com/questions/26060151/ansi-sql-standard-for-oracle-minus-keyword – Charlieface Feb 17 '21 at 19:21