How to match the list of values in an IN clause with another IN clause in a linear manner

up vote
2
down vote

favorite

--Table_1

col1 col2
............
123 abc
456 def
123 def


select * from Table_1 where col1 in (123,456) and col2 in (abc,def);

I want the output to match the row containing just '123' from "col1" and 'abc' from "col2" , and not '123' from col1 and 'def' from 'col2'.
The list in IN clause should match accordingly in a linear manner.

select * from Table_1 where col1 in (123,456) and col2 in (abc,def);

O/P

col1 col2
123 abc
456 def

edited Nov 11 at 9:25

D-Shih

24.4k61431

asked Nov 11 at 6:05

senor elanza

2110

why don't just state the condition in where clause like: where (col1 = 123 and col2 = 'abc') or (col1 = 456 and col2 = 'def')?
– assembly.jc
Nov 11 at 6:11

@assembly.jc i have a huge list to put in col1 and col2 , the above method will be tedious
– senor elanza
Nov 11 at 6:30

What's your dbms?
– D-Shih
Nov 11 at 6:37

@D-Shih Oracle sql developer
– senor elanza
Nov 11 at 6:41

@D-Shih, I was trying to understand your answer ,why did you remove it,it worked , can you write it back.
– senor elanza
Nov 11 at 9:21

|
show 1 more comment

up vote
2
down vote

favorite

--Table_1

col1 col2
............
123 abc
456 def
123 def


select * from Table_1 where col1 in (123,456) and col2 in (abc,def);

select * from Table_1 where col1 in (123,456) and col2 in (abc,def);

O/P

col1 col2
123 abc
456 def

edited Nov 11 at 9:25

D-Shih

24.4k61431

asked Nov 11 at 6:05

senor elanza

2110

why don't just state the condition in where clause like: where (col1 = 123 and col2 = 'abc') or (col1 = 456 and col2 = 'def')?
– assembly.jc
Nov 11 at 6:11

@assembly.jc i have a huge list to put in col1 and col2 , the above method will be tedious
– senor elanza
Nov 11 at 6:30

What's your dbms?
– D-Shih
Nov 11 at 6:37

@D-Shih Oracle sql developer
– senor elanza
Nov 11 at 6:41

@D-Shih, I was trying to understand your answer ,why did you remove it,it worked , can you write it back.
– senor elanza
Nov 11 at 9:21

|
show 1 more comment

up vote
2
down vote

favorite

--Table_1

col1 col2
............
123 abc
456 def
123 def


select * from Table_1 where col1 in (123,456) and col2 in (abc,def);

select * from Table_1 where col1 in (123,456) and col2 in (abc,def);

O/P

col1 col2
123 abc
456 def

edited Nov 11 at 9:25

D-Shih

24.4k61431

asked Nov 11 at 6:05

senor elanza

2110

--Table_1

col1 col2
............
123 abc
456 def
123 def


select * from Table_1 where col1 in (123,456) and col2 in (abc,def);

select * from Table_1 where col1 in (123,456) and col2 in (abc,def);

O/P

col1 col2
123 abc
456 def

sql oracle

edited Nov 11 at 9:25

D-Shih

24.4k61431

asked Nov 11 at 6:05

senor elanza

2110

edited Nov 11 at 9:25

D-Shih

24.4k61431

asked Nov 11 at 6:05

senor elanza

2110

edited Nov 11 at 9:25

D-Shih

24.4k61431

edited Nov 11 at 9:25

D-Shih

24.4k61431

edited Nov 11 at 9:25

D-Shih

24.4k61431

asked Nov 11 at 6:05

senor elanza

2110

asked Nov 11 at 6:05

senor elanza

2110

asked Nov 11 at 6:05

senor elanza

2110

why don't just state the condition in where clause like: where (col1 = 123 and col2 = 'abc') or (col1 = 456 and col2 = 'def')?
– assembly.jc
Nov 11 at 6:11

@assembly.jc i have a huge list to put in col1 and col2 , the above method will be tedious
– senor elanza
Nov 11 at 6:30

What's your dbms?
– D-Shih
Nov 11 at 6:37

@D-Shih Oracle sql developer
– senor elanza
Nov 11 at 6:41

@D-Shih, I was trying to understand your answer ,why did you remove it,it worked , can you write it back.
– senor elanza
Nov 11 at 9:21

|
show 1 more comment

why don't just state the condition in where clause like: where (col1 = 123 and col2 = 'abc') or (col1 = 456 and col2 = 'def')?
– assembly.jc
Nov 11 at 6:11

@assembly.jc i have a huge list to put in col1 and col2 , the above method will be tedious
– senor elanza
Nov 11 at 6:30

What's your dbms?
– D-Shih
Nov 11 at 6:37

@D-Shih Oracle sql developer
– senor elanza
Nov 11 at 6:41

@D-Shih, I was trying to understand your answer ,why did you remove it,it worked , can you write it back.
– senor elanza
Nov 11 at 9:21

why don't just state the condition in where clause like: where (col1 = 123 and col2 = 'abc') or (col1 = 456 and col2 = 'def')?
– assembly.jc
Nov 11 at 6:11

@assembly.jc i have a huge list to put in col1 and col2 , the above method will be tedious
– senor elanza
Nov 11 at 6:30

What's your dbms?
– D-Shih
Nov 11 at 6:37

@D-Shih Oracle sql developer
– senor elanza
Nov 11 at 6:41

@D-Shih, I was trying to understand your answer ,why did you remove it,it worked , can you write it back.
– senor elanza
Nov 11 at 9:21

|
show 1 more comment

2 Answers
2

active

oldest

votes

up vote
3
down vote

accepted

You may use tuples for comparison of a combination of multiple columns.

select *
from Table_1
where (col1,col2) in ( (123,'abc'),(456,'def'), (789,'abc') );

Demo

edited Nov 11 at 12:01

Gordon Linoff

747k34285390

answered Nov 11 at 8:14

Kaushik Nayak

16.5k41128

1

@senorelanza . . . This is the correct answer. Oracle supports tuples with in. I would recommend accepting this answer.
– Gordon Linoff
Nov 11 at 12:02

add a comment |

up vote
1
down vote

You can try to use row_number window function to make it.

SELECT col1,col2
from (
 select col1,col2,row_number() over(partition by col1 order by col2) rn
 from Table_1 
 where col1 in (123,456) and col2 in ('abc','def')
) t1
where rn = 1

sqlfiddle

edited Nov 11 at 7:59

answered Nov 11 at 6:37

D-Shih

24.4k61431

But all the values of "col2" should be manually entered by using this method
– senor elanza
Nov 11 at 6:44

@senorelanza Ok I see I edit my answer
– D-Shih
Nov 11 at 6:56

why the tables are joined by the total distinct "count", I don't understand, can you elaborate.
– senor elanza
Nov 11 at 7:18

Because cte2 will get the amount which is col2 total, cte1 will get amount by each col1 then if amount from cte2 equal to cte1 means col1 of cte1 have full amount @senorelanza
– D-Shih
Nov 11 at 7:20

Okay,but there will be multiple distinct values, like col2 can have two or three distinct values and it is not possible to join the tables based on the "cnt" of cte2 ,and also after joining, how is it possible to get records in a linear manner.
– senor elanza
Nov 11 at 7:32

|
show 4 more comments

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53246282%2fhow-to-match-the-list-of-values-in-an-in-clause-with-another-in-clause-in-a-line%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
3
down vote

accepted

You may use tuples for comparison of a combination of multiple columns.

select *
from Table_1
where (col1,col2) in ( (123,'abc'),(456,'def'), (789,'abc') );

Demo

edited Nov 11 at 12:01

Gordon Linoff

747k34285390

answered Nov 11 at 8:14

Kaushik Nayak

16.5k41128

1

@senorelanza . . . This is the correct answer. Oracle supports tuples with in. I would recommend accepting this answer.
– Gordon Linoff
Nov 11 at 12:02

add a comment |

up vote
3
down vote

accepted

You may use tuples for comparison of a combination of multiple columns.

select *
from Table_1
where (col1,col2) in ( (123,'abc'),(456,'def'), (789,'abc') );

Demo

edited Nov 11 at 12:01

Gordon Linoff

747k34285390

answered Nov 11 at 8:14

Kaushik Nayak

16.5k41128

1

@senorelanza . . . This is the correct answer. Oracle supports tuples with in. I would recommend accepting this answer.
– Gordon Linoff
Nov 11 at 12:02

add a comment |

up vote
3
down vote

accepted

You may use tuples for comparison of a combination of multiple columns.

select *
from Table_1
where (col1,col2) in ( (123,'abc'),(456,'def'), (789,'abc') );

Demo

edited Nov 11 at 12:01

Gordon Linoff

747k34285390

answered Nov 11 at 8:14

Kaushik Nayak

16.5k41128

You may use tuples for comparison of a combination of multiple columns.

select *
from Table_1
where (col1,col2) in ( (123,'abc'),(456,'def'), (789,'abc') );

Demo

edited Nov 11 at 12:01

Gordon Linoff

747k34285390

answered Nov 11 at 8:14

Kaushik Nayak

16.5k41128

edited Nov 11 at 12:01

Gordon Linoff

747k34285390

edited Nov 11 at 12:01

Gordon Linoff

747k34285390

edited Nov 11 at 12:01

Gordon Linoff

747k34285390

answered Nov 11 at 8:14

Kaushik Nayak

16.5k41128

answered Nov 11 at 8:14

Kaushik Nayak

16.5k41128

answered Nov 11 at 8:14

Kaushik Nayak

16.5k41128

1

@senorelanza . . . This is the correct answer. Oracle supports tuples with in. I would recommend accepting this answer.
– Gordon Linoff
Nov 11 at 12:02

add a comment |

1

@senorelanza . . . This is the correct answer. Oracle supports tuples with in. I would recommend accepting this answer.
– Gordon Linoff
Nov 11 at 12:02

@senorelanza . . . This is the correct answer. Oracle supports tuples with in. I would recommend accepting this answer.
– Gordon Linoff
Nov 11 at 12:02

add a comment |

up vote
1
down vote

You can try to use row_number window function to make it.

SELECT col1,col2
from (
 select col1,col2,row_number() over(partition by col1 order by col2) rn
 from Table_1 
 where col1 in (123,456) and col2 in ('abc','def')
) t1
where rn = 1

sqlfiddle

edited Nov 11 at 7:59

answered Nov 11 at 6:37

D-Shih

24.4k61431

But all the values of "col2" should be manually entered by using this method
– senor elanza
Nov 11 at 6:44

@senorelanza Ok I see I edit my answer
– D-Shih
Nov 11 at 6:56

why the tables are joined by the total distinct "count", I don't understand, can you elaborate.
– senor elanza
Nov 11 at 7:18

Because cte2 will get the amount which is col2 total, cte1 will get amount by each col1 then if amount from cte2 equal to cte1 means col1 of cte1 have full amount @senorelanza
– D-Shih
Nov 11 at 7:20

Okay,but there will be multiple distinct values, like col2 can have two or three distinct values and it is not possible to join the tables based on the "cnt" of cte2 ,and also after joining, how is it possible to get records in a linear manner.
– senor elanza
Nov 11 at 7:32

|
show 4 more comments

up vote
1
down vote

You can try to use row_number window function to make it.

SELECT col1,col2
from (
 select col1,col2,row_number() over(partition by col1 order by col2) rn
 from Table_1 
 where col1 in (123,456) and col2 in ('abc','def')
) t1
where rn = 1

sqlfiddle

edited Nov 11 at 7:59

answered Nov 11 at 6:37

D-Shih

24.4k61431

But all the values of "col2" should be manually entered by using this method
– senor elanza
Nov 11 at 6:44

@senorelanza Ok I see I edit my answer
– D-Shih
Nov 11 at 6:56

why the tables are joined by the total distinct "count", I don't understand, can you elaborate.
– senor elanza
Nov 11 at 7:18

Because cte2 will get the amount which is col2 total, cte1 will get amount by each col1 then if amount from cte2 equal to cte1 means col1 of cte1 have full amount @senorelanza
– D-Shih
Nov 11 at 7:20

Okay,but there will be multiple distinct values, like col2 can have two or three distinct values and it is not possible to join the tables based on the "cnt" of cte2 ,and also after joining, how is it possible to get records in a linear manner.
– senor elanza
Nov 11 at 7:32

|
show 4 more comments

up vote
1
down vote

You can try to use row_number window function to make it.

SELECT col1,col2
from (
 select col1,col2,row_number() over(partition by col1 order by col2) rn
 from Table_1 
 where col1 in (123,456) and col2 in ('abc','def')
) t1
where rn = 1

sqlfiddle

edited Nov 11 at 7:59

answered Nov 11 at 6:37

D-Shih

24.4k61431

You can try to use row_number window function to make it.

SELECT col1,col2
from (
 select col1,col2,row_number() over(partition by col1 order by col2) rn
 from Table_1 
 where col1 in (123,456) and col2 in ('abc','def')
) t1
where rn = 1

sqlfiddle

edited Nov 11 at 7:59

answered Nov 11 at 6:37

D-Shih

24.4k61431

edited Nov 11 at 7:59

answered Nov 11 at 6:37

D-Shih

24.4k61431

answered Nov 11 at 6:37

D-Shih

24.4k61431

answered Nov 11 at 6:37

D-Shih

24.4k61431

But all the values of "col2" should be manually entered by using this method
– senor elanza
Nov 11 at 6:44

@senorelanza Ok I see I edit my answer
– D-Shih
Nov 11 at 6:56

why the tables are joined by the total distinct "count", I don't understand, can you elaborate.
– senor elanza
Nov 11 at 7:18

Because cte2 will get the amount which is col2 total, cte1 will get amount by each col1 then if amount from cte2 equal to cte1 means col1 of cte1 have full amount @senorelanza
– D-Shih
Nov 11 at 7:20

Okay,but there will be multiple distinct values, like col2 can have two or three distinct values and it is not possible to join the tables based on the "cnt" of cte2 ,and also after joining, how is it possible to get records in a linear manner.
– senor elanza
Nov 11 at 7:32

|
show 4 more comments

But all the values of "col2" should be manually entered by using this method
– senor elanza
Nov 11 at 6:44

@senorelanza Ok I see I edit my answer
– D-Shih
Nov 11 at 6:56

why the tables are joined by the total distinct "count", I don't understand, can you elaborate.
– senor elanza
Nov 11 at 7:18

Because cte2 will get the amount which is col2 total, cte1 will get amount by each col1 then if amount from cte2 equal to cte1 means col1 of cte1 have full amount @senorelanza
– D-Shih
Nov 11 at 7:20

Okay,but there will be multiple distinct values, like col2 can have two or three distinct values and it is not possible to join the tables based on the "cnt" of cte2 ,and also after joining, how is it possible to get records in a linear manner.
– senor elanza
Nov 11 at 7:32

But all the values of "col2" should be manually entered by using this method
– senor elanza
Nov 11 at 6:44

@senorelanza Ok I see I edit my answer
– D-Shih
Nov 11 at 6:56

why the tables are joined by the total distinct "count", I don't understand, can you elaborate.
– senor elanza
Nov 11 at 7:18

Because cte2 will get the amount which is col2 total, cte1 will get amount by each col1 then if amount from cte2 equal to cte1 means col1 of cte1 have full amount @senorelanza
– D-Shih
Nov 11 at 7:20

Okay,but there will be multiple distinct values, like col2 can have two or three distinct values and it is not possible to join the tables based on the "cnt" of cte2 ,and also after joining, how is it possible to get records in a linear manner.
– senor elanza
Nov 11 at 7:32

|
show 4 more comments

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Odtnhj