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!

 

2 thoughts on “Using Inner Join and Where Clause

Leave a Reply

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