Issue with creating oracle query

Question

I am having problem coming up with following query.

id| prop_id| obj_id| value|
1         7       2      1
2         8       2      1
4         7       5      7 
5         8       5      12

Input parameters to the query are:

• Collection of (prop_id, value) pairs

We must select records whose prop_id and values match input parameters: but there is also one constrain, the obj_id of resulting records must be same. If it is not same, result must be empty. Otherwise it must return obj_id.

Example:

Let's say input parameters are: (7,1) and (8,12).

Now there are two records with such values: first and last. However, obj_id of first record is 2 and obj_id of second record is 5. Hence, the result must be empty because 2!=5.

Another example:

Let's say input parameters are: (7,7) and (8,12).

Now there are two records with such values: last two records. obj_id of first is 5 and obj_id of second record is also 5. Hence, the result must be 5.

Answer 1

Try this

select obj_id from table 
where (prop_id=7 and value=7) or (prop_id=8 and value=12)
group by obj_id having count(*)=2 and count(distinct object_id)=1