Home » SQL & PL/SQL » SQL & PL/SQL » How to get a Key based on exact matching value combination (Oracle 10g)
How to get a Key based on exact matching value combination [message #431869] Thu, 19 November 2009 13:23 Go to next message
sumit.sethi83
Messages: 2
Registered: November 2009
Junior Member
Hi All,

I have a table which has 2 columns (Key and value) and let me take a sample data before i explain my problem:

Key Value
a 1
a 2
b 1
b 3
c 1
c 2
c 3

Now my problem statement is that in my procedure i get list of values as input in an array.
lets say i get 2 values as input (1 and 2). Now i need to find exact Key which matches just these 2 values.
Solution should return "a" as the result only.
Re: How to get a Key based on exact matching value combination [message #431871 is a reply to message #431869] Thu, 19 November 2009 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now i need to find exact Key which matches just these 2 values.

Quote:
Solution should return "a" as the result only.

???

select "a" from dual;
Isn't it the solution?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: How to get a Key based on exact matching value combination [message #431973 is a reply to message #431871] Fri, 20 November 2009 04:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's one way of doing it:
create table test_103 (k varchar2(1), v number);

insert into test_103 values ('a', 1);
insert into test_103 values ('a', 2);
insert into test_103 values ('b', 1);
insert into test_103 values ('b', 3);
insert into test_103 values ('c', 1);
insert into test_103 values ('c', 2);
insert into test_103 values ('c', 3);

select k from test_103 where v in (1,2)
minus
select k from test_103 where v not in (1,2);
Re: How to get a Key based on exact matching value combination [message #432030 is a reply to message #431973] Fri, 20 November 2009 09:26 Go to previous messageGo to next message
sumit.sethi83
Messages: 2
Registered: November 2009
Junior Member
Thanks for this approach and it works for the sample input values (1 and 2).
But if i change values to (1, 2 and 3), then i expect output to be c, where this query will return wrong results.
Re: How to get a Key based on exact matching value combination [message #432033 is a reply to message #432030] Fri, 20 November 2009 10:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A simple misunderstanding, largely springing from your fairly terse example.

Download Tom Kyte's Stragg, and you can do this:
select k
      ,stragg(v)
from  (select k,v
       from   test_103
       order by k,v)
group by k
having stragg(v) = '1,2,3'
Previous Topic: query fetching wrong results
Next Topic: Selecting the maximum value from the Group (merged 3)
Goto Forum:
  


Current Time: Sat Oct 01 02:34:39 CDT 2016

Total time taken to generate the page: 0.05180 seconds