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!

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