Search code examples
sqlpostgresqlrelational-division

PostgreSQL IN all

I have the following table structure:

| id | object_id | status_id |
------------------------------
| 1  |     12    |     1     |
| 2  |     12    |     2     |
| 3  |     18    |     5     |

I need to select all object_id that were in status 1 and 2. That is, something like this: select object_id from table_name where status_id in (1, 2), but I need status_id to be not in one of the listed values, but exactly in both. That is, from the above table, I should return the value 12 (cause object_id in that statuses equals 12). How can this be done?

Solution

  • To get those object IDs that have exactly those two status values, you can use

    select object_id
from the_table
group by object_id
having bool_and(status_id in (1,2))
   and count(distinct status_id) = 2

    Online example