Find SQL table rows where there are multiple different values

I want to be able to filter out groups where the values aren’t the same. When doing the query:

SELECT 
    category.id  as category_id, 
    object.id    as object_id,
    object.value as value
FROM
    category,
    object
WHERE
    category.id = object.category

We get the following results:

 category_id | object_id | value
-------------+-----------+-------
       1     |     1     |     1
       1     |     2     |     2
       1     |     3     |     2
       2     |     4     |     3
       2     |     5     |     2
       3     |     6     |     1
       3     |     7     |     1

The goal: Update the query so that it yields:

 category_id 
-------------
       1     
       2

In other words, find the categories where the values are different from the others in that same category.

I have tried many different methods of joining, grouping and so on, to no avail.

I know it can be done with multiple queries and then filter with a little bit of logic, but this is not the goal.

Answer

You can use aggregation:

SELECT o.category as category_id
FROM object o
GROUP BY o.category
HAVING MIN(o.value) <> MAX(o.value);

You have left the FROM clause out of your query. But as written, you don’t need a JOIN at all. The object table is sufficient — because you are only fetching the category id.

Advertisement

Answer