Menu
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
530
The "ambiguous column" error means that there's a reference to a column identifier, and MySQL has two (or more) possible columns that match the specification. In this specific case, it's the reference to CategoryID column in the WHERE clause.
One of the simplest ways to solve an “ambiguous name column” error — without changing column name — is to give the tables you want to join an alias. This sends a clear information to the SQL Machine the columns are different.
To fix this, simply add the tablename or alias for the table you want to work with. If you are writing the query yourself, this is a bit easier to deal with as you will know which table you meant to use. In our example, we should add the alias for the oc_customer table, c, to the column names.
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.
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.
199
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
