CODEWITHSUNDEEP
mysql
idungotnosn
idungotnosn

Reputation: 2047

MySQL saying that column is ambiguous

I need to do a query on the same field on a MySQL table but with different parameters. This is my query. The issue that comes up is that MySQL is saying that users_name is ambiguous.

SELECT projects_id, 
projects_users_id,projects_companies_id,projects_name,
projects_description,project_registered_date,
projects_users_id_register,
users.users_name as userInCharge,companies.companies_name as company,
users.users_name as user_register FROM projects 
LEFT JOIN users as userInCharge ON (users_id = projects_users_id) 
LEFT JOIN users as register ON (users_id=projects_users_id_register)
LEFT JOIN companies ON (companies_id = projects_companies_id) ORDER BY projects_id DESC

My goal is to get the ID of both the person in charge of the project and the person who registered the project. How do I do this?

Upvotes: 0

Views: 61

Answers (3)

user1864610
user1864610

Reputation:

Your ambiguity is arising because of these LEFT JOIN clauses:

LEFT JOIN users as userInCharge ON (users_id = projects_users_id) 
LEFT JOIN users as register ON (users_id=projects_users_id_register)

You need to specify where users_id is coming from (yes - I know they're from the same table, but SQL is a bit slow sometimes) Try:

LEFT JOIN users as userInCharge ON (userInCharge.users_id = projects_users_id) 
LEFT JOIN users as register ON (register.users_id=projects_users_id_register)

Upvotes: 2

Gustek
Gustek

Reputation: 3760

You JOIN with users table as userInCharge and register so use these names when You specify columns

userInCharge.users_name as userInCharge
register.users_name as user_register

instead of

users.users_name as userInCharge
users.users_name as user_register

Upvotes: 0

mucio
mucio

Reputation: 7119

Use the table alias and not users.users_name.

And also in the joins.

Upvotes: 0

Related Questions