EXTRACT DATA [message #325397] |
Fri, 06 June 2008 02:16  |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
Hi All,
I have two tables namely MRH, MRJ. I need to extract all datas from MRJ where MRH.RONO=MRJ.RON0 and MRJ.JCODE='T0107'.
I need the output like this
RONO LICNO JCODE PRICE
R001 ABC T0107 20
R001 ABC ZO300 20
R001 ABC V9901 30
R002 XYZ T0107 20
R002 XYZ Z0300 10
I need the output like this. I tried with my SQL knowledge but unable to extract. Can anyone help me? I have attached the table script with insert data as attachment RO_TKP.SQL.
-
Attachment: RO_TMP.SQL
(Size: 1.81KB, Downloaded 484 times)
|
|
|
|
|
|
Re: EXTRACT DATA [message #325405 is a reply to message #325403] |
Fri, 06 June 2008 02:33   |
kumarvk
Messages: 214 Registered: July 2004
|
Senior Member |
|
|
Dear All,
I need the data from MRJ for all the RONO only for the JCODE having T0107. Did you see my required output? I tried this method already and also using where clause but unable to get all the datas.
Please see my required output for your reference.
|
|
|
Re: EXTRACT DATA [message #325408 is a reply to message #325397] |
Fri, 06 June 2008 02:36   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Thanks for posting the create & insert scripts - it helps.
The requirements you have specified do not produce the output you want from the data you have given us.
The requirements you gave give this query:SQL> select mrj.*
2 from mrj,mrh
3 where mrj.rono = mrh.rono
4 and mrj.jcode = 'T0107';
RONO JCODE PRICE
---------- ----- ----------
R001 T0107 20
R002 T0107 20
To get the output you want, a simple join should do:SQL> select mrj.*
2 from mrj,mrh
3 where mrj.rono = mrh.rono;
RONO JCODE PRICE
---------- ----- ----------
R001 T0107 20
R001 Z0300 20
R001 V9901 30
R001 J9901 10
R002 T0107 20
R002 Z0300 10
R003 Z0300 10
|
|
|
Re: EXTRACT DATA [message #325412 is a reply to message #325408] |
Fri, 06 June 2008 02:44  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Ahhh - I think I have managed to work backwards from your results to derive the question you meant to ask.
I think you are asking: How can I get all the MRJ records that have a RONO for which there is a MRJ record with a code of 'T0107'
You can do this quite simply with a self join of MRJ to itself.SQL> select mrj1.rono
2 ,mrh.licno
3 ,mrj1.jcode
4 ,mrj1.price
5 from mrj mrj1
6 ,mrj mrj2
7 ,mrh
8 where mrj1.rono = mrh.rono
9 and mrj2.jcode = 'T0107'
10 and mrj1.rono = mrj2.rono;
RONO LICNO JCODE PRICE
---------- ---------- ----- ----------
R001 ABC J9901 10
R001 ABC V9901 30
R001 ABC Z0300 20
R001 ABC T0107 20
R002 XYZ Z0300 10
R002 XYZ T0107 20
6 rows selected.
|
|
|