By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,198 Members | 1,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,198 IT Pros & Developers. It's quick & easy.

Join problem (5.0.4 beta)

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.