?
Solved

Cross Outer Join

Posted on 2016-09-06
4
Medium Priority
?
82 Views
Last Modified: 2016-09-27
Hi,

   I have a table A with field1 that I want to left join into other tables B and C (B & C are queries resultsets). After , matching up this field1 with table B, I want to concat column B2 values for all rows matched on the field1. Similarly for table C - match on field1 from table A and then pick all matched rows in C and jam the column C2 values together with a delimiter say ;.

 tblA.field1  tbB.B2;tblB.B2   tblC.C2;tblC.C2

I was thinking of Cross outer apply in MS SQL, but need an equivalent syntax in Oracle 10.
0
Comment
Question by:LuckyLucks
  • 2
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 41786567
Something like this?

select tblA.field1, listagg(tblB.B2,';') within group (order by tblB.B2),    listagg(tblC.C2,';') within group (order by tblC.C2)
from tblA
left outer join tblB
on tblA.field1 = tblB.field1
left join tblC
on tblA.field1 = tblC.field1
group by tblA.field1
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 41786571
or use subqueries

assuming tblA.field1 is unique try this...


SELECT tbla.field1,
       (SELECT LISTAGG(tblb.b2, ';') WITHIN GROUP (ORDER BY tblb.b2)
          FROM tblb
         WHERE tblb.field1 = tbla.field1)
           b2list,
       (SELECT LISTAGG(tblc.c2, ';') WITHIN GROUP (ORDER BY tblc.c2)
          FROM tblc
         WHERE tblc.field1 = tbla.field1)
           c2list
  FROM tbla
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41790859
"Cross outer apply" does not exist

I guess you mean either an "outer apply" or "cross apply"

In oracle 12c i believe you can use:

"cross join lateral"

But as yet i still have not had a need to use it.
0
 

Author Closing Comment

by:LuckyLucks
ID: 41819044
thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

621 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