What mean "Column '[field]' in field list is ambiguous in MySQL?

Question

Hey I have a Problem in my SQL Statement

select Objektnr, AVG(Mieter.Miete) as "Durchschnitt Miete" from Objekt
join Wohnung on Objekt.Objektnr = Wohnung.Objektnr
join Mieter on Wohnung.Wohnungsnr=Mieter.Wohnungsnr

My Join don't work :(

Here is the Error: #1052 - Column 'Objektnr' in field list is ambiguous

score 2 · Answer 1 · answered Nov 02 '15 at 18:36

The issue is that you are saying you want to select the field Objektnr, but it exists in more than one table. It's ambiguous because it doesn't know which one to pick from. You need to fully qualify your column so that it knows which one to select - and since these are the same between the two tables (due to the JOIN), the following should work:

Select   Objekt.Objektnr, 
         AVG(Mieter.Miete) as "Durchschnitt Miete" 
From     Objekt
Join     Wohnung on Objekt.Objektnr = Wohnung.Objektnr
Join     Mieter  on Wohnung.Wohnungsnr = Mieter.Wohnungsnr

score 0 · Answer 2 · answered Nov 02 '15 at 18:36

means you have same field in diferent tables

just include the table before the field

select Objekt.Objektnr, AVG(Mieter.Miete) as "Durchschnitt Miete" from Objekt
join Wohnung on Objekt.Objektnr = Wohnung.Objektnr
join Mieter on Wohnung.Wohnungsnr=Mieter.Wohnungsnr

What mean "Column '[field]' in field list is ambiguous in MySQL?

2 Answers2