'SQL query to get rows that does not comply with given rule

I have a list of products in my database.

PRODUCTID UNITS
PROD1 1
PROD2 2
PROD3 3
PROD4 1
PROD5 1

Each of them are sold in one or multiple units of measure.

UNITOFMEASUREID
Kg
Pc
Pk
Ctn
Bx
PRODUCTID UNITOFMEASUREID ISBASEUNIT
PROD1 Kg False
PROD2 Kg True
PROD2 Pk False
PROD3 Kg True
PROD3 Pc False
PROD3 Ctn False
PROD4 Ctn False
PROD5 Bx True

All of the products need one of the units to be marked as the base unit of measure. Unfortunately the quality of the data is not 100%. And I need to find all products that does not have any base units of measure set.

In the above scenario I would like to write an SQL-query that give the me the two lines marked in bold.

PRODUCTID UNITOFMEASUREID ISBASEUNIT
PROD1 Kg False
PROD4 Ctn False

I have tried to write some SQL including "GROUP BY" but my knowledge is not good enough to solve this one on my own. Hopefully someone else might help me out.

sql


Solution 1:[1]

We can use MAX and GROUP by. Because T is after F in the alphabet if one of the values is True then MAX for that productID is True. We then use HAVING to get the values where the MAX is not True.
There products are therefore the products which do not have a unit of measure marked True, whether the value is False, empty or null.

SELECT 
  productid,
  MAX(isbaseunit)
FROM 
  UNITOFMEASUREID
GROUP BY
  productid
HAVING
  MAX(isbaseunit) <> 'True';

Solution 2:[2]

This is my final, working query. Thanks @Kendle.

SELECT
  productid,
  MAX(CONVERT(varchar, isbaseunit)) as Units
FROM
  UNITOFMEASUREID
GROUP BY
  productid
HAVING
  MAX(CONVERT(varchar, isbaseunit)) <> 'True'
AND
  MAX(CONVERT(varchar, isbaseunit)) = 0

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Martin at Mennt

Related Questions