Posted by J.O. Aho on 02/27/06 03:07

lallous wrote:
> Even my last correction was wrong....
>
> I think this one works, is it the best though?
>
> SELECT
> T.rowid, N1.name, N2.name, N3.name
> FROM
> TABLE2 AS T
> INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
> INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
> INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
> WHERE
> T.rowid = 1
> AND
> N1.id = T.nameid1
> AND
> N2.id = T.nameid2
> AND
> N3.id = T.nameid3

You are doing things twice here,
the "AND N1.id = T.nameid1" has already been done at "ON T.nameid1 = N1.id".

And you will get the same column names fro N1.name, N2.name and N3.name when
you run the query in mysql (all will be listed as name), so AS for each column
is useful to easier to see the difference between the columns.

>>> SELECT T.rowid, N1.name AS Name1, N2.name AS Name2, N3.name AS Name3
>>> FROM TABLE2 AS T
>>> INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
>>> INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
>>> INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id

And just add the WERE statement that is wished for for the query.


//Aho

[Back to original message]