Solved

Oralce query question

Posted on 2011-02-19
2
318 Views
Last Modified: 2012-05-11
I have these sub-queries in Oracle (11g):

(select ID, F1 FROM ..) p
(select ID, F2 FROM ..) q
(select ID, F3 FROM ..) r
(select ID, F4 FROM ..) s

From which I need the O/P like:
ID   F1    F2   F3   F4
-----------------------
1    6      17     null null
2    null   3       8    null
3    null  4        7    2
4    3     6       11   23
5    8     null     null 22

ID is unique in the O/P.The above comes from these input values:
p:
Id  F1
-------
1   6
4   3
5   8

q:
Id  F2
-------
1  17
2  3
3  4
4  6

r:
Id  F3
-------
2   8
3   7
4   11

s:
Id  F4
-------
3   2
4   23
5  22
0
Comment
Question by:toooki
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 450 total points
ID: 34935081
Why could you not use the select from the other question?

Just use a simple union:
SELECT ID, MIN(F1) AS F1, MIN(F2) AS F2, MIN(F3) AS F3
FROM
(
select id,f1, null f2, null f3, null f4 from p
union all
select id,null, f2, null f3, null f4 from q
union all
select id,null, null f2, f3, null f4 from r
union all
select id, null, null f2, null f3, f4 from s
)
GROUP BY ID
order by id
/
0
 

Author Comment

by:toooki
ID: 34935200
It worked perfectly. Thank you. I changed the line
MIN(F1) AS F1, MIN(F2) AS F2, MIN(F3) AS F3
changed from MIN to MAX.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

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.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

808 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