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

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.

CodePudding user response：

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';

CodePudding user response：

You could write a subquery to pull PRODUCTID that have a base unit of measure.

Then write a query to pull from your table where PRODUCTID is not in <your subquery>

CodePudding user response：

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