How to create a database query?

Question

PostgreSQL

The table consists of an object id and a property id.
It is necessary to pull out lines with objects that have properties specified by the user during the search.

Example: pull out objects that have properties 1 and 4 (the result of the query should get six rows - highlighted in green in the figure)


The problem is that the number of properties can be different (up to 15 pieces). You need a generic script. How to be?

Answer 1

It is possible like this:

with objects as (
  select idObject
  from tbl
  where idProperty in (1,4)
  group by idObject
  having  count(distinct idProperty) = 2
) 
select * from tbl join objects using(idObject);

PostgreSQL CTE fiddle