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?
 
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
 
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
 
PortletPaulfreelancerCommented:
"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.