I need help with a join to a table - Programmers Heaven

Howdy, Stranger!

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

Categories

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

  • mikeleibomikeleibo Posts: 4Member
    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.