SQL join not working
Question
Application Type
Reactive
Select top(@MaxRecords) [Id], [Name], [Type], [Reference], [Description], [CreationDate], [CreatedBy], [Entity]
from (
SELECT {Documents}.[Id], {Documents}.[Name], {Documents}.[Type], {Documents}.[Reference], {Documents}.[Description], {Documents}.[CreationDate], {Documents}.[CreatedBy], {Documents}.[Entity]
from {Documents}
where ...
order by ...
OFFSET @StartIndex  ROWS FETCH NEXT @MaxRecords  ROWS ONLY
) a
LEFT JOIN {DocumentFiles}
ON a.[Id] = {DocumentFiles}.[DocumentId]

I have this query above, i deleted the where / order by.. The query works fine without the join, but when i try to do 

LEFT JOIN {DocumentFiles}
ON a.[Id] = {DocumentFiles}.[DocumentId]


it gives me the following error:

Database returned the following error: Error in advanced query GetDocuments2: Ambiguous column name 'ID'. 


Any help is appreciated! :)

Hi Ricardo,


use a.XXX in your main SELECT statement. This error is happening because you are selecting [Id], but both table "a" and {DocumentFiles}  have the same column name


Select top(@MaxRecords) a.[Id], a.[Name], a.[Type], a.[Reference], a.[Description], a.[CreationDate], a.[CreatedBy]....



Cheers


Hello Ricardo, 

This error occurs because you have the same attribute name in both tables. Try to see in this article how you can solve this problem. Hope this helps.

https://towardsdatascience.com/how-to-solve-the-ambiguous-name-column-error-in-sql-d4c256f3d14c

Regards,

Jorge Rodrigues

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.