How to write a query using multiple 'IN' or any other keywords in SQL so that the condition being applied will be based upon the order defined

Question

I want to fetch records from the database as :

select * from table where column1 IN (aa, bb , cc...) and column2 IN (34, 55, 66...) and column3 IN(df, fg, hh...);

so if I use this query it will fetch record for me based upon the combinations, for example it will give me a record where column1= aa, column2= 55, and column3 = hh. so this combination I don't want.

I want the combinations to be as: column1 =aa , column2 =34, column3=df.

only first data in IN statements to be matched, similarly second data of each In statements to be checked and so on. I don't need other combinations where in IN statement column1 first data is matched with column2 second data and fetch be the records. Or any other operator to use.

My main purpose is to use this Sql for JPA in java so that i can pass arrayList to respective IN statements and get the data as:

List<Object> tableList = findByColumn1InAndColumn2InAndColumn3In(arrayList1, arrayList2, arrayList3);

Any suggestions would be helpful. Thanks.

Answer 1

One way is using OR

select * 
from table 
where column1 = aa and column2 = 34 and column3 = df
   or column1 = bb and column2 = 55 and column3 = fg
   or ..

Answer 2

You can fetch the first condition with IN (xx,xx,xx) to match and then use the operator AND

SELECT * FROM DB
WHERE column1 IN (aa, bb, cc) AND column2 = "aa" AND column3 = "bb" -- and so on