Link to home
Start Free TrialLog in
Avatar of Sthokala
SthokalaFlag for United States of America

asked on

Oracle query Question

Hi,
   I have 3 tables attached in the code snippet .
I want to have the out put like this by joining the three columns


   Source_id   Name   Version   Source_data   Part_id
      11             Test1     2              part                  123
      12             Test2     2              Fault                 145  
      13             null       null            Report              152
      14             null       null            Hours               null


I want to get max of version as version from Verify_test table. Please let me know how can I build a query with these tables. Please let me know if this is not clear.

Thanks


Table Verify_test
id    Source_id   Name   Version
1       11        Test1   1
2       11        Test1   2  
3       12        Test2   1 
3       12        Test2   2   


Table Source_test

Source_id    Source_data
  11            part
  12            Fault
  13            Report
  14            Hours

Table Part_test

Part_id    Source_id    Source_data
   123        11            part
   145        12            Fault
   152	      13            Report

Open in new window

Avatar of Alex Matzinger
Alex Matzinger
Flag of United States of America image

Select  vt.Source_id,   vt.Name,   vt.Version,   st.Source_data,   pt.Part_id
from Verify_test vt, Source_test st, Part_test pt, (select id, max(version) from Verify_test) mv
where vt.Source_id = st.Source_id
   AND pt.Source_id = st.Source_id
   AND mv.id =vt.id
   AND mv.id = vt.id
Avatar of Sthokala

ASKER

Hi amatzinger,
   Thanks very much for your quick reply. when I run this I am getting ORA-00937 : not a single group function error. Please let me know how can I do this.

Thanks
Np, Sorry for the error...

the inner select statement should be:

select id, max(version)
from Verify_test
Group by id

That should create a list of all ID's with just the Maximum value for the version for each ID.
Thanks for your reply. I am just getting only two records...I need to have some kind of outerjoin to get all 4 rows..Please help me in getting the result.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Alex Matzinger
Alex Matzinger
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot for your response..the only thing I changed is

 select source_id,max(id) id, max(version)
      from Verify_test
      Group by source_id.


Thanks again for your quick replies.