Solved

Oracle query Question

Posted on 2011-02-11
6
327 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now