Cross Outer Join

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.
LuckyLucksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
"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
LuckyLucksAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.