MySQL 'user_id' in where clause is ambiguous problem

HoMe picture HoMe · May 7, 2010 · Viewed 77.4k times · Source

How can I correct the problem I keep getting from the code below which states 'user_id' in where clause is ambiguous. Thanks for the help in advance.

Here is the mysql table.

SELECT user.*, user_info.* 
FROM user 
INNER JOIN user_info ON user.user_id = user_info.user_id
WHERE user_id=1

Answer

Daniel Vassallo picture Daniel Vassallo · May 7, 2010

You simply need to specify which user_id to use, since both the user_info and user table have a field called user_id:

... WHERE user.user_id=1

SQL wouldn't tolerate this ambiguity, since for what it knows, both fields can represent totally different data.