MySQL self-join table error #1066 - Not unique table/alias: 'cities_translation'

Question

I'm having a table for users and a table for orders and a table for cities names which is cities_translation. In users table I have user id, first name, lastname, city id and representative id. The representative is just another user in the same table. what I want to do is to join these tables to get a table like this

user id   first name    last name    city name    representative    wholesaler

  1         foo            bar        city1            2             yes
  2         user           user       city2            0             no

The expected result is

user id   first name    last name    order id    city name    representative 

  1         foo            bar          1         city1            user

Query:

Select 
  orders.id, orders.user_id, orders.total, orders.final_total, orders.order_status_id, 
  orders.unix_time, u.id,u.email, u.first_name, u.last_name,m.first_name as rep_firstname,
  m.last_name as rep_lastname, u.representative_id,u.city_id,cities_translation.* 
From orders,cities_translation,users u 
  left join cities_translation 
Where u.city_id = cities_translation.city_id
  And orders.user_id = u.id  
  And cities_translation.lang_id='2' 
  And orders.order_status_id='1' 
Left join users r on u.representative_id = r.id 
Group by orders.user_id 
Limit 5

But the result is: "#1066 - Not unique table/alias: 'cities_translation'" So how to rewrite this query to avoid this error and get representative name of each wholesaler

Don't ever mix implicit and explicit join syntax! Use only the proper syntax of a join , it will help you avoid this problems. Also, alias **every** table differently , then you won't have this problem. — sagi, Nov 06 '16 at 14:31

score 2 · Answer 1 · answered Nov 06 '16 at 14:31

You are mixing explicit and implict join

From orders,cities_translation,users u 
left join cities_translation

and you should adopt the proper explict join format ..

Anyway the error message is related to ten fact that you use two time the table cities_translation (in from and in left join )

if you need this table two time you should use proper alias for refer to each single istance of the table and use the correct alias as prefix for the related column name

From orders,cities_translation as a ,users u 
left join cities_translation  as b

e4c5 · Answer 2 · 2016-11-06T14:48:30.917

1

As the error suggests, you have to create a unique alias for the table that is being joined twice.

Select 
  orders.id, orders.user_id, orders.total, orders.final_total, orders.order_status_id, 
  orders.unix_time, u.id,u.email, u.first_name, u.last_name,m.first_name as rep_firstname,
  m.last_name as rep_lastname, u.representative_id,u.city_id,cities_translation.* 
From orders,cities_translation ct1,users u 
  left join cities_translation ct2
ON u.city_id = ct1.city_id   /** you probably wanted ON here INSTEAD OF WHEN*/
  And orders.user_id = u.id  
  And ct2.lang_id='2' 
  And orders.order_status_id='1' 
Left join users r on u.representative_id = r.id 
Group by orders.user_id 
Limit 5

Take care to change ct1, and ct2 as appropriate.

Side notes: It's customary to write SQL reserved words in upper case and column table names in lower case.
You are mixing explicit and implicit joins. That always leads to confusion when you are reading the code later. Try to use explicity joins when ever possible.
This form of GROUP BY will not work on mysql 5.7+

edited Nov 06 '16 at 14:48

answered Nov 06 '16 at 14:31

e4c5

52,766
11
101
134

a where clause between joins and a group by without any aggregation is ok? – P.Salmon Nov 06 '16 at 14:44
oops @P.Salmon thanks for pointing out! That would of course produce another error – e4c5 Nov 06 '16 at 14:45
error: "near 'Where u.city_id = ct1.city_id And orders.user_id = u.id And ct2.lang_id=' at line 7" – PHP User Nov 06 '16 at 14:46
Its MySQL, so this GROUP BY form is not recommended but possible. The joins on the other hand.. @P.Salmon – sagi Nov 06 '16 at 14:46
Yes PHP USER, that's another error in your original query as @P.Salmon has just pointed out. A separate question really, but I think you probably meant to use 'ON' there – e4c5 Nov 06 '16 at 14:47
Just fix the syntax for him, don't post a NOT WORKING query, because that's what it is right now. You can't use the `ON` clause to join the first tables of an implicit join, that has to happen on the `WHERE` (shouldn't happen at all). – sagi Nov 06 '16 at 14:55
After using ON instead of Where " #1051 - Unknown table 'cities_translation'" and when I changes cities_translation to ct1.* I got: #1054 - Unknown column 'ct1.city_id' in 'on clause' – PHP User Nov 06 '16 at 14:56
@PHPUser we don't know what exactly your join conditions are. You asked about the alias problem me and the other user told you how to solve it but where ct1 should appear and where ct2 should appear depends on your requirements. Your original query was wrong to begin with (other than the non unique alias) so how do you expect us to figure it out?? – e4c5 Nov 06 '16 at 15:00

MySQL self-join table error #1066 - Not unique table/alias: 'cities_translation'

2 Answers2