More than two INNER JOINS

Question

Below I'm inserting a code example that I would like to know if the syntax is correct for the third JOIN. I know that it works for two JOINS. My question is if you have more than two, you just keep on adding like the line of the third JOIN.

<cfquery name="query1" datasource"database">
SELECT column1, column2, column3, t1.XXID, t2.YYID, t3.ZZ1ID
FROM (MainTable mt INNER JOIN Table1 t1 ON mt.XXID = t1.XXID)
INNER JOIN Tabel2 t2 ON mt.YYID = t2.YYID
INNER JOIN Tabel3 t3 ON mt.ZZID = t3.ZZID
</cfquery>

Also for the next example

<cfquery name="query1" datasource"database">
SELECT column1, column2, column3, t1.XXID, t1.YYID, t1.ZZ1ID
FROM (MainTable mt INNER JOIN Table1 t1 ON mt.XXID = t1.XXID)
INNER JOIN Tabel1 t1 ON mt.YYID = t1.XXID
INNER JOIN Tabel1 t1 ON mt.ZZID = t1.XXID
</cfquery>

Thanks
Manuel Lino

Anonymous · Accepted Answer

The best way to do this is to set up three tables:
(1) A jobs table that just has job related info.
(2) An Employee table, that just has employee info.
(3) And a relation table that links employees to jobs. This is part of a "normalized" design and will save you much grief later.

See the attached code.

Anonymous · Answer

First, for questions like this, please state which RDBMS you are using because the syntax varies -- especially with MS Access.

Second, the "next example" is not valid because it declares t1 more than once.

The first example is OK except that it has parenthesis that may cause an error and it has some unscoped columns.

Use the attached (SQL standard form; works for most, not all, RDBMS):

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.