How to resolve ambiguous error in the query-CodePudding

I am using laravel framework for developing API's ,i have one query that is executed without where condition without any error i need to execute with where condition but it's throwing an error

query

select count(*) as aggregate
from `users`
  left join `books` on `books`.`book_id` = `books`.`id`
where `access_id` = 5054

SQL Error [1052] [23000]: Column 'access_id' in where clause is ambiguous

after searching google i got something we have to specify reference of a table name , i added reference name like this where users.access_id =5054 but it's throwing an error like unknown column but in my db i have that column in both users and books table

CodePudding user response：

The problem is its consider as a column so that's why syntax error is coming,try following way it will resolve your problem

select count(*) as aggregate
from `users`
  left join `books` on `books`.`book_id` = `books`.`id`
where `users`.`access_id` = 5054