Howdy, Stranger!

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

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.