Solved

Cross Outer Join

Posted on 2016-09-06
4
52 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 73

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 73

Accepted Solution

by:
sdstuber earned 500 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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 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

11 Experts available now in Live!

Get 1:1 Help Now