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

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

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.

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...

• 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...