odp.net SQL query retrieve set of rows from two input arrays.

Question

I have a table with a primary key consisting of two columns. I want to retrieve a set of rows based on two input arrays, each corresponding to one primary key column.

select pkt1.id, pkt1.id2, ... from PrimaryKeyTable pkt1, table(:1) t1, table(:2) t2
where pkt1.id = t1.column_value and pkt1.id2 = t2.column_value

I then bind the values with two int[] in odp.net.

This returns all different combinations of my resulting rows. So if I am expecting 13 rows I receive 169 rows (13*13). The problem is that each value in t1 and t2 should be linked. Value t1[4] should be used with t2[4] and not all the different values in t2.

Using distinct solves my problem, but I'm wondering if my approach is wrong. Anyone have any pointers on how to solve this the best way? One way might be to use a for-loop accessing each index in t1 and t2 sequentially, but I wonder what will be more efficient.

Edit: actually distinct won't solve my problem, it just did it based on my input-values (all values in t2 = 0)

Answer 1

Assuming you want all rows where the key exists in both tables at any position.

select pkt1.id, pkt1.id2, ... 
from PrimaryKeyTable pkt1
where pkt1.id in (select column_value from table(:1)) 
and pkt1.id2 in (select column_value from table(:2)) 

Answer 2

This question is solved now. Here is the answer if you're interested.

select id1, id2 from t, (select rownum rn1, a1.* from table(:1) a1) a1, (select rownum rn2, a2.* from table(:2) t2) t2 where (id1, id2) in ((a1.column_value, a2.column_value)) and rn1 = rn2

http://forums.oracle.com/forums/thread.jspa?threadID=1083982&tstart=15