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

SthokalaAsked:
Who is Participating?
 
Alex MatzingerConnect With a Mentor Database AdministratorCommented:
ok, i didn't realize you also wanted the null columns.  you need to do a union then with this query:

Select  vt.Source_id,   vt.Name,   vt.Version,   st.Source_data,   pt.Part_id
from Verify_test vt, Source_test st, Part_test pt
where vt.Source_id = st.Source_id
   AND pt.Source_id = st.Source_id
   AND st.Source_id NOT IN(Select distinct Source_ID from Verify_test)

The above will return all entries that do not have a version.  So the complete query will look like this:

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 
      Group by id) 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

UNION

Select  vt.Source_id,   vt.Name,   vt.Version,   st.Source_data,   pt.Part_id
from Verify_test vt, Source_test st, Part_test pt 
where vt.Source_id = st.Source_id
   AND pt.Source_id = st.Source_id
   AND st.Source_id NOT IN(Select distinct Source_ID from Verify_test)

Open in new window


That should do the trick.
0
 
Alex MatzingerDatabase AdministratorCommented:
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
0
 
SthokalaAuthor Commented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Alex MatzingerDatabase AdministratorCommented:
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.
0
 
SthokalaAuthor Commented:
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
0
 
SthokalaAuthor Commented:
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.
0
All Courses

From novice to tech pro — start learning today.