Making a SQL query that selects of list of values from more than one column

I have a table which is something like this:

AccountNum	Name	RoutingNum
a1	name1	r1
a2	name2	r2
a2	name3	r1

I want to select all rows with a specific pair account number and routing number, for example :

input
List<accountNum, routingNumber> pairList = {<a1, r1>, <a2, r2>}

sql returns:
| AccountNum | Name           | RoutingNum      |
| --------   | -------------- |--------------
| a1         | name1          | r1              |
| a2         | name2          | r2              |

For some context I just want to make a single call, that I would be making using jdbc, this is my java code which only selects for account number, which is not what I want as I want to select using routingNum too:

String inSql = String.join(",", Collections.nCopies(plainAccountNumberEntries.size(), "?"));
        List<String>accountNumberList = Arrays.asList("a1", "a2");

        return ddsJdbc.query(
                String.format("SELECT * from table where AccountNum in (%s)", inSql),
                accountNumberList.toArray(),
                new someMapper()
        );

I want to avoid making multiple calls to the database for every entry in the list.

Thanks for your time.

Answer

Use an IN condition with multiple expressions in the list:

SELECT *
from   table_name
where  ( AccountNum, RoutingNum ) in ( ( 'A1', 'R1' ), ( 'A2', 'R2') )

Advertisement

Answer