Solved

Cross Outer Join

Posted on 2016-09-06
4
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

729 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