CODEWITHSUNDEEP
sqloracle-databaseplsql
Cal
Cal

Reputation: 867

SQL Multiple ID columns in the Where...IN condition

HI I may not use correct search keywords to find the answer on stackoverflow. Here's my question: One table has 3 (or more) ID columns. With a list of the IDs I would like to query the table to return rows.

For example MyTable has 3 ID columns:

ID1   ID2       ID3
15    567       1312
15    567       1319
21    918       1582
58    609       1444

In PLSQL using TUPLE we can do

SELECT * FROM mytable WHERE (ID1, ID2) IN ((1,2), (2,3)...);

But Tuple doesn't apply to this situation because there are 3 ID columns.

What I'm doing now is to concatenate the IDs in the query because IDs are numeric:

SELECT * FROM mytable WHERE (ID1 || '-' || ID2 || '-' || ID3) IN (('15-567-1312'), ('21-918-1582')...);

But I'm always wondering if there is another way to do it?

Thank you for your time.

Upvotes: 0

Views: 2883

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

And why can't tuples have three columns? Did you try this?

SELECT *
FROM mytable
WHERE (ID1, ID2, ID3) IN ( (1, 2, 3), (2, 3, 4), ... );

Upvotes: 4

Related Questions