-1

I have a very simple table with product_id of a product and its height, width, and depth. Product_id can exist multiple times in a table which I'm using, hence I've used distinct statement. However, the combination of height, width and depth for a particular product_id should be unique.

Example given below: For product_id 1003 it is okay, because it has only one dimension combination (6,2,2) For product_id 1002 it is not okay, because it has two combinations of dimensions (7,3,3 and 9,3,3)

How to tell sql to show ONLY these records like 1002, where for one product_id we have multiple dimensions combination?

Thank you very much in advance for help.

TylerDurden887
  • 1

1 Answers1

0

You can use group by and having:

select product_id
from t
group by product_id
having min(height) <> max(height) or
       min(width) <> max(width) or
       min(depth) <> max(depth);

If you wanted the list of rows in the original table, then a different approach is recommended -- using exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.product_id = t.product_id and
                    (t2.height <> t.height or t2.width <> t.width or
                     t2.depth <> t.depth
                    )
             );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi, thank you very much for your help. Unfortunately, the first query is showing me no records. Height, width and depth are set up as "float". Can this be a reason why this query returns no values ? – TylerDurden887 Aug 24 '17 at 12:35
  • @TylerDurden887 . . . It can be. Two values that look the same might actually differ by a very small amount if they are represented as floats. – Gordon Linoff Aug 27 '17 at 21:13