Thread: How Do You Do a Three Way Join?

I've got three tables, <jqz>, <zqj>, and <abc>.  I can do a
<jqz j RIGHT JOIN zqj z> and a <zqj z LEFT JOIN abc a>, but is there a
way to do a three-way join to get the columns of all three tables?  Of
course I could do one of those former two joins and then join the out-
put table to the third table, but is there a way to do it without cre-
ating the intermediate table?

I tried:

     SELECT
         j.abc, j.def, z.xyz, a.ghi
       FROM
         jqz j RIGHT JOIN zqj z LEFT JOIN abc a
       ON
         j.abc = z.abc AND z.xyz = a.xyz;

but <psql> complains about a syntax error "at or near" that last semi-
colon.  Anybody know what I'm doing wrong, or what I can do to get my
desired three-way join?  Or do I have to create that intermediate ta-
ble?  Any information you can give me would be appreciated.

                                ---Kevin

"You'll never get to heaven, or even to LA,
if you don't believe there's a way."
from _Why Not_

--- kvnsmnsn@cs.byu.edu wrote:

> I've got three tables, <jqz>, <zqj>, and <abc>.  I can do a
> <jqz j RIGHT JOIN zqj z> and a <zqj z LEFT JOIN abc a>, but is there a
> way to do a three-way join to get the columns of all three tables?

Take a look at "FULL OUTER JOIN".

http://www.postgresql.org/docs/8.2/interactive/queries-table-expressions.html#QUERIES-JOIN

Regards,
Richard Broersma Jr.

On Tue, 22 May 2007 kvnsmnsn@cs.byu.edu wrote:

> I've got three tables, <jqz>, <zqj>, and <abc>.  I can do a
> <jqz j RIGHT JOIN zqj z> and a <zqj z LEFT JOIN abc a>, but is there a
> way to do a three-way join to get the columns of all three tables?  Of
> course I could do one of those former two joins and then join the out-
> put table to the third table, but is there a way to do it without cre-
> ating the intermediate table?
>
> I tried:
>
>      SELECT
>          j.abc, j.def, z.xyz, a.ghi
>        FROM
>          jqz j RIGHT JOIN zqj z LEFT JOIN abc a
>        ON
>          j.abc = z.abc AND z.xyz = a.xyz;

I think you'd want something like
 (jqz j RIGHT JOIN zqj z ON j.abc=z.abc) LEFT JOIN abc a ON (z.xyz=a.xyz)