Often times when setting up database tables it’s easy to organize the same foreign key names in different tables. It’s even harder to maintain discipline in your MySQL calls by specifying the table name before the field name. I spent about 30 minutes trying to sort this out the other day and it turned out to be a simple mistake.
I was trying to do an inner join on two tables with a “WHERE” clause. I was using something called “author_id” in both tables and failed to specify which table for the where clause. I kept getting a mysql error:
Column 'author_id' in where clause is ambiguous
For one reason or another I didn’t think to specify the table name on the where clause until I did some research. So this is just a reminder…If you’re doing joins on tables with the same field names, it helps to specify the table name in the where clause.
Wrong…
SELECT * FROM bookmarks INNER JOIN articles ON articles.id=bookmarks.article_id WHERE author_id=1
Right…
SELECT * FROM bookmarks INNER JOIN articles ON articles.id=bookmarks.article_id WHERE bookmarks.author_id=1
That’s it. Just a reminder. If you see an error “Column is ambiguous” in your SQL output, make sure to use those table names!
But this WHERE clause is showing wrong result when number of IDs are more than 1.
However, you explained it very nicely.
Than you So Much sir.
Спасибо, то что искал
Thankyou so so much for writing this blog on sql inner join and where clause. By only reading what mistake you were making I got my solution. And now my query is running without any error.