Mysql Inner Join... Column error 'Id_occurrence' in field list is ambiguous

Question

I want to access data from two different tables: ocorrencia and fotos.

• On the table fotos I have a field that is the id_ocorrencia (which creates the relation - foreign key).
• On the table ocorrencia no foreign table key fotos.

When I perform this query:

select 
  nome_foto 
from 
  ocorrencia 
inner join fotos 
  on fotos.id_ocorrencia=ocorrencia.id_ocorrencia

works, but when I run this:

select 
  Id_ocorrencia, nome_foto 
from
  ocorrencia 
inner join fotos 
  on fotos.id_ocorrencia=ocorrencia.id_ocorrencia

go wrong

Answer 1

As the error message already says the column id_ocorrencia is defined ambiguously, that is, it exists in more than one table and you are not defining from which table the database should obtain the information. Simply add table aliases to make the database understand your instruction:

SELECT f.Id_ocorrencia,
       f.nome_foto
  FROM ocorrencia o
 INNER JOIN fotos f ON f.id_ocorrencia = o.id_ocorrencia

Answer 2

The column exists in two tables, the database does not know which one to show, the ambiguity occurs.

Add the alias of the table you want to present the data:

select ocorrencia.id_ocorrencia, nome_foto from ocorrencia inner join fotos on fotos.id_ocorrencia=ocorrencia.id_ocorrencia

Answer 3

select ocorrencia.Id_ocorrencia, nome_foto from ocorrencia inner join fotos on fotos.id_ocorrencia=ocorrencia.id_ocorrencia

Missing alias before ID_OCORRENCIA field selection>