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.
This works on my DB2 (version 9.7 on Linux/Unix/Windows) by using this syntax:
SELECT * FROM PLAYERS WHERE (First_Id, Second_Id) IN (VALUES (1,1), (1,2), (1,3))
This syntax won't work on DB2 on the Mainframe (at least in version 9.1) because you can't substitute a sub-select with a VALUES expression. This syntax will work:
SELECT * FROM PLAYERS WHERE (First_Id, Second_Id) IN (SELECT 1, 1 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 1, 2 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 1, 3 FROM SYSIBM.SYSDUMMY1)
Here's a very similar solution in postgresql:
SELECT tmp_table.val1, tmp_table.val2 FROM tmp_table WHERE (tmp_table.val1, tmp_table.val2) not in (select tmp_table2.val1, tmp_table2.val2 from tmp_table2);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With