Triple Table Join

[former_member205400]

Experts, the following queries all work:

select a.infocube, a.compid, a.author

from rsrrepdir a

select b.bname, b.persnumber, b.name_text

from user_addrp b

JOIN A and B

select a.infocube, a.compid, a.author, b.name_text

from rsrrepdir a join user_addrp b

on a.author = b.bname

select c.persnumber, c.smtp_addr

from adr6 c

JOIN B and C

select b.name_text, c.smtp_addr

from user_addrp b join adr6 c on b.persnumber = c.persnumber

But when I try to join the 3 tables together I'm getting an invalid relation operator:

select a.infocube, a.compid, a.author, b.name_text, c.smtp_addr
from
rsrrepdir a join user_addrp b on a.author = b.bname and
user_addrp b join adr6 c on b.persnumber = c.persnumber

and missing keyword when I try this:

select a.infocube, a.compid, a.author, b.name_text, c.smtp_addr
from
rsrrepdir a join user_addrp b, user_addrp b join adr6 c
on a.author = b.bname and b.persnumber = c.persnumber

Any insight to where I'm going wrong?

Mike

[lbreddemann · Accepted solution]

>

> But when I try to join the 3 tables together I'm getting an invalid relation operator:
> 

> select a.infocube, a.compid, a.author, b.name_text, c.smtp_addr
> from
> rsrrepdir a join user_addrp b on a.author = b.bname and
> user_addrp b join adr6 c on b.persnumber = c.persnumber
> 
> and missing keyword when I try this:
> 

> select a.infocube, a.compid, a.author, b.name_text, c.smtp_addr
> from
> rsrrepdir a join user_addrp b, user_addrp b join adr6 c
> on a.author = b.bname and b.persnumber = c.persnumber
> 
> 
> Any insight to where I'm going wrong?
> 
> Mike

Hi Mike,

the problem is that you seem to mixup how multiple join statements are used in ANSI SQL and in the non-standard WHERE-clause format.

Make sure to keep the correct join syntax:

...
FROM
  tableA  *join* tableB
   *on* tableA.col1 = tableB.col1
   *and* tableA.col2 = tableB.col2
*join* tableC 
  *on* tableB.col3 = tableC.col1
  *and* ...

WHERE
...

So, your join should probably look like this:

Select a.infocube, a.compid, a.author, b.name_text, c.smtp_addr
from
    rsrrepdir a 
join user_addrp b 
   on a.author = b.bname 
join adr6 c 
   on b.persnumber = c.persnumber

There is no AND between multiple JOIN clauses in ANSI SQL syntax.

Regards,

Lars

[former_member205400 · Accepted solution]

Lars,

You are the man!!!

Exactly what I needed was a little tweak. Thank you very very much.

Mike