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 |
Use EXISTS
to get a productID
with more than 1 productName
s:
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 productName
s 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
);