Skip to content

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 :

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),
                new someMapper()

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

Thanks for your time.


Use an IN condition with multiple expressions in the list:

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