Find SQL table rows where there are multiple different values

Question

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.

See group by .. having count. What database implementation? — user2864740
– user2864740, Commented Aug 14, 2020 at 16:12
Show the query where you are grouping, and we tell you what you got wrong. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 14, 2020 at 16:15
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged — marc_s
– marc_s, Commented Aug 14, 2020 at 16:16

Gordon Linoff · Accepted Answer · 2020-08-14 16:16:27Z

1

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.

answered Aug 14, 2020 at 16:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hultan Over a year ago

I missed the FROM clause, it's been added. Your query worked like a charm!

Collectives™ on Stack Overflow

Find SQL table rows where there are multiple different values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related