0

In Oracle SQL I need to filter records by pair of multiple values using SELECT query. There is a FIRST_ID and SECOND_ID and I want to have data filtered by only specif pair.

I tried using CONCAT in first way, next i prepared a lot of pairs with OR operator, but both ways need a lot of manual works.

select * 
from table_data 
where to_char(first_id||;||second_id) in ('123;354', '422;563', ... '353;536');

or 

select * 
from table_data 
where (first_id = 123 and second_id = 354) 
or (first_id = 422 and second_id = 563) 
or (first_id = 353 and second_id = 536);

So, You see that I cant'use two IN operators (one for first_id, second for second_id) because it will give a result for all crosing pairs like 123 - 254, 123 - 562 & 123-536 etc. Any ideas how to do it fast and easy?

mkRabbani
  • 16,295
  • 2
  • 15
  • 24
Piotr R
  • 61
  • 1
  • 2
  • 1
    When you say *"a lot of manual work"* do you mean the amount of typing it takes to write the IN clause? Any solution in which the values are hard-coded will entail lots of typing. So our question to you is: where do these values come from? How easy could it be to implement something cleverer? – APC Jun 26 '19 at 15:53
  • Yes, it's a lot of typing and I understand it but this Gordon answer is what I exactly expected. I couldn't find it before. – Piotr R Jun 27 '19 at 08:21

1 Answers1

3

Oracle supports IN with tuples:

select *
from table_data
where (first_id, second_id) in ( (123, 354), (422, 563), (353, 536));
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786