Join problem (5.0.4 beta)

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


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.

FROM  clients LEFT OUTER JOIN country ON =
salutations LEFT OUTER JOIN clients ON clients.salutation =
WHERE   clients.client_id = :id

MySQL returns the error SQLSTATE = S1T00 Not unique table/alias:

Can anyone shed any light?
Powerbuilder 10
MySQL 5.0.4 beta
MyODBC 3.51.11-2

Re: Join problem (5.0.4 beta)

On 22/05/2005, Gerry wrote:

Quoted text here. Click to load it

Are you sure you don't want

LEFT OUTER JOIN salutations ON salutations.salut = clients.salutations


Re: Join problem (5.0.4 beta)

Gerry wrote:
Quoted text here. Click to load it

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 =
  salutations AS s LEFT OUTER JOIN clients AS c2 ON c2.salutation =
  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.

Bill K.

Re: Join problem (5.0.4 beta)

Thanks guys, for the response.

Yes, you are correct, the outer/inner joins were not too logical as I
was trying all possible combinations to get mysql to accept the select
I am embarrassed to say I have found the problem. I had a good nights
sleep and started afresh with the mysql manual instead of trying to
get the powerbuilder generated code to work.
I am afraid that the conversion Powerbuilder/Sybase -> MySQL is not as
easy as I at first thought.
Thank you for spending time listening and I am sorry to have wasted
your time with a bit of a dumb question.


Quoted text here. Click to load it

Site Timeline