MySQL 'user_id' in where clause is ambiguous problem
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开发者_StackOverflow中文版.user_id
WHERE user_id=1
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.
I have same error and i have resolved it by change:
var extra = 'AND (STATUS = 1 OR STATUS IS NULL)';
to
var extra = 'AND (ORDW.STATUS = 1 OR ORDW.STATUS IS NULL)';
When i join more than 2 tables.
The solution is to select each column explicitly everywhere. Don't use user.* and user_info.* in your select list, either:
SELECT u.user_id, u.user_fudge, ui.foo, ui.bar
FROM user u
INNER JOIN user_info ui
ON ui.user_id = u.user_id
WHERE u.user_id = 1;
You should be mention alias name of your column and assign to user_id like
SELECT user.*, user_info.*
FROM user as u
INNER JOIN user_info as ui ON u.user_id = ui.user_id
WHERE u.user_id=1
精彩评论