Multiple select from a result table into a single row in oracle

Question

I was struggling to find a way to replace multiple JOIN with inner select in SQL command but nothing found up to now. I have a query which joins 11 tables and due to multiple roles I have to have 21 joins in my query. so I decided to find more information in my tables and found out I have a table which has proper information to relate some tables. [my result table ][1]
as you can see I have a field named "Mainuuid" which is relevant with different "Slave" values. Each slave value is primary key in it's relative table. So what I need is a single row which is computed from multiple select based on the result image for example: I named the result table as "test"

SELECT a,b FROM person WHERE Id=test.slave,
SELECT x,y FROM Unit WHERE Id=test.slave,
SELECT o,p FROM naturalperson WHERE Id=test.slave

And I want to put all result in a single row! any suggestion will be appreciated.

[1]: https://i.stack.imgur.com/7pK2D.jpg

I think it is like a scanning rows and finding required fields from a relative table and adding fetched columns to the result and so on. — hamed.jahanyan, Jan 11 '16 at 05:50

score 0 · Answer 1 · answered Jan 11 '16 at 06:12

0

I don't really follow why you really need to replace joins with nested SELECT's, but here's my suggestion:

SELECT x.mainuuid, max(x.a),max(x.b),max(y.x),max(y.y),max(z.o),max(z.p)
FROM
(SELECT mainuuid, a,b
FROM person, test
WHERE Id=test.slave) x
(SELECT mainuuid, x,y
FROM Unit, test
WHERE Id=test.slave) y
(SELECT mainuuid, o,p
FROM naturalperson, test
WHERE Id=test.slave) z
WHERE x.mainuuid = y.mainuuid(+) 
AND x.mainuuid = z.mainuuid(+)
GROUP BY x.mainuuid;

I haven't had time to test this, but the point is, that you use the max function to eliminate null values and get the results from all tables on the same row based on mainuuid. This was a quick answer - might not be the solution, but I hope it at least can help you forward.

answered Jan 11 '16 at 06:12

David Bachmann Jeppesen

395
1
8

I tried it but you know after second select the tables are not related to each other! first FROM happen to just first table not others! – hamed.jahanyan Jan 11 '16 at 06:36
Okay - I don't think i understand your question then. :/ – David Bachmann Jeppesen Jan 11 '16 at 06:39
I'll just make one thing clear: You can't select data from more than one table without making a join. If you leave out the joins, the database will make a cross join. – David Bachmann Jeppesen Jan 11 '16 at 06:58

score 0 · Answer 2 · edited May 23 '17 at 11:44

There are more ways to achieve that, the most straightforward would be:

SELECT (SELECT a FROM person WHERE Id=test.slave FETCH FIRST 1 ROW ONLY),
       (SELECT x FROM Unit WHERE Id=test.slave FETCH FIRST 1 ROW ONLY),
       (SELECT o FROM naturalperson WHERE Id=test.slave FETCH FIRST 1 ROW ONLY)

The problem is, that you can select only one attribute per nested select. The way to solve this would be OUTER APPLY or CROSS APPLY, in Oracle available form version 12C.

SELECT p.a, p.b, u.*, n.* 
FROM person p
OUTER APPLY (
  SELECT u1.x,u1.y FROM Unit u1 WHERE u1.Id=p.Id FETCH FIRST 1 ROW ONLY) u
OUTER APPLY (
  SELECT n1.o,n1.p FROM naturalperson n1 WHERE n1.Id=p.Id FETCH FIRST 1 ROW ONLY) n
WHERE p.Id=test.slave

For older versions of Oracle use Lateral joins and WHERE ROWNUM=1 instead of FETCH FIRST 1 ROW ONLY.

Multiple select from a result table into a single row in oracle

2 Answers2