How to write a query using multiple 'IN' or any other keywords in SQL so that the conditio-CodePudding

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.

CodePudding user response：

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 ..

CodePudding user response：

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