Composite key, in comparison

Question

I have three fields that form a unique composite key on a table.

I want to pass in 3 different arrays, where the index matches.

custIds= [0,1,2]
custLetters = [A,B,C]
products = ["Cheese","lemons","Aubergine"]

is there one sql statement that will return all three rows (assuming they exists), just combining via in won't work to due to "false positives" :

select * from mytable 
where custId in (custIds)
 and custLetters in (custLetters)
and product in (products);

database oracle, but via hibernate hql, so ansi preferred if possible ?

Answer 1

You could compose your arrays into a single one, after that:

custIds= [0,1,2]
custLetters = [A,B,C]
products = ["Cheese","lemons","Aubergine"]

Key=["0ACheese","1Blemons","2CAubergine"]

select * from mytable 
where custId+custLetters+product in (Key);

Answer 2

OT: your SQL query is probably wrong. It should be:

select * from mytable 
where (custId, custLetters, product) 
in ( (0, 'A', 'Cheese'),
 (1, 'B', 'lemons'),
 (2, 'C', 'Aubergine'));

I'm not use whether Hibernate can generate such a query. But in is just a syntax sugar for conjuctions and disjunctions.