Using Inner Join and Where Clause

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!

 

4 thoughts on “Using Inner Join and Where Clause

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.