sqldatabasesqlitegroup-bydatabase-optimization

How to filter inconsistent records in sqlite


Say I have SQLite table with the following records:

recID productID productName
1 1 Product A
2 2 Product B
3 2 Product C
4 3 Product D
5 3 Product D

recID = primary key, auto increment.

If I run:

SELECT productID, productName 
FROM table 
GROUP BY productID, productName

Result is:

productID productName
1 Product A
2 Product B
2 Product C
3 Product D

As you can see, productID 2 has inconsistent productName: Product B and Product C. How do I run query just to detect the inconsistent ones? Eg I want the result to be:

productID productName
2 Product B
2 Product C

Solution

  • Use EXISTS to get a productID with more than 1 productNames:

    SELECT t1.productID, t1.productName 
    FROM tablename t1
    WHERE EXISTS (
      SELECT *
      FROM tablename t2
      WHERE t2.productID = t1.productID AND t2.productName <> t1.productName 
    );
    

    Or, for a small dataset use aggregation in a subquery which counts the distinct number of productNames of each productID, with the operator IN:

    SELECT productID, productName 
    FROM tablename
    WHERE productID IN (
      SELECT productID
      FROM tablename
      GROUP BY productID
      HAVING COUNT(DISTINCT productName) > 1
    );