I need help with a join to a table

Problem: I have to do a select on two tables - one that has to join back to itself. I can't code it right.

Table A looks like this: aa,ab,ac,ad...ay,az
Table B looks like this: aa, bb, bc, bd ...

The result table should look like this:

aa,
ab,
bb,
bc,
ab(from A row where aa = ay)
ab(from A row where aa = az),
ae,
af,
ag.

I tried a number of selects but can't even get past edit.

First I used a case statement:
Select A.ab,A.ac,B.bb,B.bc,
Case
when ay = ' ' then ' '
else select ab from c.A where c.aa = a.xy.
End case
, Case
when az = ' ' then ' '
else select ab from c.A where c.aa = a.xz.
End case
, ae , af, ag
from a.A b.B

Then I tried Union All with a subselect on the selected set.

Select A.ab,A.ac,B.bb,B.bc,ay,az,ae,af from a.A B.b Union all

case
if ay null and az null
Select A.ab,A.ac,B.bb,B.bc,' ' , ' ',ae,af from A A, B B
end case
case
if ay null and az not null
Select A.ab,A.ac,B.bb,B.bc,' ' ,
select c.ab from ' ',ae,af from A A, B B
end case

etc...

Comments

  • Problem: I have to do a select on two tables - one that has to join back to itself. I can't code it right.

    Table A looks like this: aa,ab,ac,ad...ay,az
    Table B looks like this: aa, bb, bc, bd ...

    The result table should look like this:

    aa, ab, bb, bc,
    ab(from A row where aa = ay) , : ab(from A row where aa = az),
    ae, af, ag.

    If the rows look like this.

    AA1,AB1,null,null
    AA2,AB2,null,AA3
    AA3,AB3,AA2,null

    The result has to look like this.

    AB1,'-','-'
    AB2,AB3,'-'
    AB3,'-',AB2


    I tried a number of selects but can't even get past edit.
    :
    : First I used a case statement:
    : Select A.ab,A.ac,B.bb,B.bc,
    : Case
    : when ay = ' ' then ' '
    : else select ab from c.A where c.aa = a.xy.
    : End case
    : , Case
    : when az = ' ' then ' '
    : else select ab from c.A where c.aa = a.xz.
    : End case
    : , ae , af, ag
    : from a.A b.B
    :
    : Then I tried Union All with a subselect on the selected set.
    :
    : Select A.ab,A.ac,B.bb,B.bc,ay,az,ae,af from a.A B.b Union all

    case
    if ay null and az null
    Select A.ab,A.ac,B.bb,B.bc,' ' , ' ',ae,af from A A, B B
    end case
    case
    if ay null and az not null
    Select A.ab,A.ac,B.bb,B.bc,' ' ,
    select c.ab from ' ',ae,af from A A, B B
    end case

    etc...



Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion