-1

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?

First Sin
  • 94
  • 6

3 Answers3

1
select object_id 
from table_name 
where status_id in (1, 2)
group by object_id 
having count(distinct status_id) = 2
juergen d
  • 201,996
  • 37
  • 293
  • 362
1

If you just need unique object id from status 1 or 2 then you can use distinct keyword.:

select distinct object_id from table_name where status_id in (1, 2)
Srijon Chakraborty
  • 2,007
  • 2
  • 7
  • 20
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
  • That returns the wrong result https://dbfiddle.uk/?rdbms=postgres_13&fiddle=e39f96636c0b7084afbc659861648d2d –  Feb 17 '21 at 06:43
  • it returns object_id if status is 1 or two. I mentioned that in my answer. uf desired answer is to have distinct object_id which have both 1 and 2 in status field then group by is a easy solution. – Kazi Mohammad Ali Nur Romel Feb 17 '21 at 06:50
1

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