Register Login

How to solve “Column 'user_id' in field list is ambiguous” error?

While working with tables in SQL and trying to fetch some data, you might have come across the Column 'user_id' in field list is ambiguous” error. In this post, we will take a closer look at the reason behind the error followed by its solution.

What is Column 'user_id' in field list is ambiguous” error?  

This error occurs when you are trying to fetch some data from multiple tables with the help of a join query. But if the same field name is present in both tables, and you are not passing the table name as part of the column identifier, the query will be unsuccessful.

Look at the example given below:

SQL Query

SELECT user_id, name, age, user_address, user_sex FROM user_details as ud, users as u WHERE user_id = user_id LIMIT 0, 25

Error

#1052 - Column 'user_id' in field list is ambiguous

Column User Id Error

In the above example, we are trying to fetch data from two tables, users and user_details by using table join.   

users

 

user_details

user_id

 

user_details_id

name

 

user_id

age

 

user_address

 

 

user_sex

And you can see, the name of one field user_id is present in both the tables.

In the SELECT query, you are not specifying table name to select data. So, MySQL gets confused and generates the Column 'user_id' in field list is an ambiguous error.

Solution

SELECT u.user_id, u.name, u.age, ud.user_address, ud.user_sex FROM user_details as ud, users as u WHERE u.user_id = ud.user_id

In this solution, you can see that the table name is specified using dot (.). Aliases u is for users table and ud is for user_details table. So, MySQL does not get confused after encountering u.user_id and ud.user_id. The query is executed successfully.