I want to be able to filter out groups where the values aren’t the same. When doing the query:
x
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.
Advertisement
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.