471,075 Members | 1,284 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

Join problem (5.0.4 beta)

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'.

Can anyone shed any light?
Thanks,
Gerry.
Powerbuilder 10
MySQL 5.0.4 beta
MyODBC 3.51.11-2
Jul 23 '05 #1
3 1492
On 22/05/2005, Gerry wrote:
SELECT ....
FROM clients
LEFT OUTER JOIN country ON clients.country = country.country_code, salutations LEFT OUTER JOIN clients ON clients.salutation =
salutations.salut


Are you sure you don't want

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

--
felix
Jul 23 '05 #2
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.
Jul 23 '05 #3
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.

Gerry.
On Sun, 22 May 2005 17:54:50 +0200, Gerry <ge****@xs4all.nl> 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'.

Can anyone shed any light?
Thanks,
Gerry.
Powerbuilder 10
MySQL 5.0.4 beta
MyODBC 3.51.11-2


Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Sebastian Ware | last post: by
1 post views Thread by Eric Sassaman [MS] | last post: by
1 post views Thread by jake | last post: by
reply views Thread by Heiko Wundram | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.