Gerry wrote:
Hi,
I am trying to convert a Powerbuilder program from Sybase SQLA to
MySQL. The following select works fine with Sybase but I can't get
MySQL to accept it.
SELECT ....
FROM clients LEFT OUTER JOIN country ON clients.country =
country.country_code,
salutations LEFT OUTER JOIN clients ON clients.salutation =
salutations.salut
WHERE clients.client_id = :id
MySQL returns the error SQLSTATE = S1T00 Not unique table/alias:
'clients'.
That error is normal. You've used the clients table twice in your join,
so the parser can't tell which one is supposed to be the one used in the
condition in your WHERE clause.
Try using row aliases:
SELECT ....
FROM clients AS c LEFT OUTER JOIN country AS co ON c.country =
co.country_code,
salutations AS s LEFT OUTER JOIN clients AS c2 ON c2.salutation =
s.salut
WHERE c.client_id = :id
Also, I do not see any join condition between the first two tables and
the latter two tables; you're creating a cartesian product. This
doesn't have anything to do with the error you got, and in fact it is
not an error. But I'm guessing it's not going to give you the result
you want.
I'm also curious why you are using outer joins. For example, I would
not expect there to be country codes in the clients table that don't
appear in the country table. I would guess the country table is a
lookup table, containing all possible country codes. This should be a
superset of the countries mentioned in records in the client table.
The outer join with salutations makes even less sense.
Regards,
Bill K.
