Alex Rivera | Logout

Select Query by Pair of fields using an in clause

Asked 2012-05-23T18:38:51.330
41

I have a table called players as follows:

First_Id    Second_Id     Name
1           1             Durant
2           1             Kobe
1           2             Lebron
2           2             Dwight
1           3             Dirk

I wish to write a select statement on this table to retrieve all rows whose first ids and second ids match a bunch of specified first and second ids.

So for example, I wish to select all rows whose first and second ids are as follows: (1,1), (1,2) and (1,3). This would retreive the following 3 rows:

First_Id    Second_Id    Name
1           1            Durant
1           2            Lebron
1           3            Dirk

Is it possible to write a select query in a manner such as:

SELECT * 
FROM PLAYERS
WHERE (First_Id, Second_Id) IN ((1,1), (1,2) and (1,3))?

If there is a way to write the SQL similar to the above I would like to know. Is there a way to specify values for an IN clause that represents multiple rows as illustrated.

I'm using DB2.

Edit
Report

1 Answer

2

With compound primary keys, I would concatenate the two ids and match compound strings.

select id1 + id2 as FullKey, *
from players
where FullKey in ('11','12','13')

(If ids are not strings, simply cast them as such.)

answered 2012-05-23T19:08:27.773

Your Answer