Solved

Oracle query Question

Posted on 2011-02-11
6
330 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Sthokala
  • 3
  • 3
6 Comments
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 34874626
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
 

Author Comment

by:Sthokala
ID: 34874687
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
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 34874719
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Sthokala
ID: 34874776
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
 
LVL 4

Accepted Solution

by:
Alex Matzinger earned 500 total points
ID: 34874853
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
 

Author Comment

by:Sthokala
ID: 34875472
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question