Home » SQL & PL/SQL » SQL & PL/SQL » Finding matching records from another table (Oracle 10g)
Finding matching records from another table [message #405673] Fri, 29 May 2009 00:11 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to find the testcode and rate from the table MEMS_INDV_RATES_TEMP based on mecode and meapplno supplied.
Here I m taking the example (MECODE = '00020040' and meapplno = '100')
The testcodes and rates from table MEMS_INDV_RATES_TEMP where MECODE = '00020040' and meapplno = '100' are as :=
          TESTCODE         RATE
          LIP              150
          GYQ              125

Then I have to find the matching ratecodes from the table MEMS_EXAM_RAT_DETINDV_STR where all the testcode and
fee should match to the testcode and rate from the table MEMS_INDV_RATES_TEMP.

The ratecodes with testcode and fee in table MEMS_EXAM_RAT_DETINDV_STR are as :-
RATECODE     TESTCODE       FEE
--------------------------------------
DL01           FSD           125
DL01           GYQ           125
DL01           LIP           150
DN01           GYQ           150
DN01           LIP           150
KR01           GYQ           125
KR01           LIP           150


The correct matching ratecodes where all the testcode and fee match are 'DL01' and 'KR01'.

Please find the table create script and inserts of the two tables.
CREATE TABLE MEMS_INDV_RATES_TEMP
(
  MECODE      VARCHAR2(8),
  MEAPPLNO    VARCHAR2(15),
  TESTCODE    VARCHAR2(4),
  RATE        NUMBER(17,2),
  ISELIGIBLE  CHAR(1)
)

INSERT INTO MEMS_INDV_RATES_TEMP ( MECODE, MEAPPLNO, TESTCODE, RATE,
ISELIGIBLE ) VALUES ( 
'00020040', '100', 'LIP', 150, 'Y'); 
INSERT INTO MEMS_INDV_RATES_TEMP ( MECODE, MEAPPLNO, TESTCODE, RATE,
ISELIGIBLE ) VALUES ( 
'00020040', '100', 'GYQ', 125, 'Y'); 
COMMIT;
     
CREATE TABLE MEMS_EXAM_RAT_DETINDV_STR
(
  RATECODE     VARCHAR2(4 BYTE)                 NOT NULL,
  TESTCODE     VARCHAR2(3 BYTE)                 NOT NULL,
  FEE          NUMBER(17,2)                     NOT NULL
 )
 
 

INSERT INTO MEMS_EXAM_RAT_DETINDV_STR ( RATECODE, TESTCODE, FEE ) VALUES ( 
'KR01', 'LIP', 150); 
INSERT INTO MEMS_EXAM_RAT_DETINDV_STR ( RATECODE, TESTCODE, FEE ) VALUES ( 
'KR01', 'GYQ', 125); 
INSERT INTO MEMS_EXAM_RAT_DETINDV_STR ( RATECODE, TESTCODE, FEE ) VALUES ( 
'DL01', 'LIP', 150); 
INSERT INTO MEMS_EXAM_RAT_DETINDV_STR ( RATECODE, TESTCODE, FEE ) VALUES ( 
'DL01', 'FSD', 125); 
INSERT INTO MEMS_EXAM_RAT_DETINDV_STR ( RATECODE, TESTCODE, FEE ) VALUES ( 
'DN01', 'LIP', 150); 
INSERT INTO MEMS_EXAM_RAT_DETINDV_STR ( RATECODE, TESTCODE, FEE ) VALUES ( 
'DL01', 'GYQ', 125); 
INSERT INTO MEMS_EXAM_RAT_DETINDV_STR ( RATECODE, TESTCODE, FEE ) VALUES ( 
'DN01', 'GYQ', 150); 
COMMIT;


I thought of a way but not sure how to do this :-
select b.ratecode, b.testcode, b.fee from MEMS_INDV_RATES_TEMP a, MEMS_EXAM_RAT_DETINDV_STR b
 where a.testcode = b.testcode
   and a.rate = b.fee


This returns all matching records for different ratecodes.

I can get the count of number of records in table MEMS_INDV_RATES_TEMP that is two here and if somehow I can fins the ratecodes where count is same as table MEMS_INDV_RATES_TEMP.

Please tell me a way to do that.
Thanks for looking into this,

Mahi

[Updated on: Fri, 29 May 2009 00:24]

Report message to a moderator

Re: Finding matching records from another table [message #405679 is a reply to message #405673] Fri, 29 May 2009 00:40 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Does this help you?
SELECT DISTINCT ratecode
FROM   mems_exam_rat_detindv_str me
WHERE  NOT EXISTS (SELECT testcode,
                          rate
                   FROM   mems_indv_rates_temp
                   MINUS
                   SELECT testcode,
                          fee
                   FROM   mems_exam_rat_detindv_str
                   WHERE  ratecode = me.ratecode);
By
Vamsi
Re: Finding matching records from another table [message #405684 is a reply to message #405679] Fri, 29 May 2009 01:00 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks Vamsi,
Your query worked Smile

Thanks a lot for looking into this,

Mahi
Previous Topic: Onlogon Database triggers
Next Topic: using Dbms_Job to call the procedure
Goto Forum:
  


Current Time: Sat Dec 10 10:49:29 CST 2016

Total time taken to generate the page: 0.16804 seconds