Mikheil Tchelidze Mikheil Tchelidze - 4 months ago 9
SQL Question

Issue with creating oracle query

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

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