Solved

Oracle query Question

Posted on 2011-02-11
6
333 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

615 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