Clever Geek Handbook

Incorrect syntax near '.' when using Coalesce

I am trying to join three tables, I have successfully joined two.

This work (two tables)

SELECT  ImportExportBadFile.ID, ImportExportSettingID,
    ImportExportBadFile.UserID,
    Coalesce(UserName,'') UserName  
    FROM
    ImportExportBadFile 
    LEFT OUTER JOIN Users HGSQLUsers ON ImportExportBadFile.UserID = HGSQLUsers.ID
    ORDER BY  ImportExportBadFile.DateTimeStamp DESC
  • Username belongs to the users table

Now that I tried to add another table:

SELECT  ImportExportBadFile.ID, ImportExportSettingID,
    SETT.Name,
    ImportExportBadFile.UserID,
    Coalesce(UserName,'') HGSQLUsers.UserName  
    FROM
    ImportExportBadFile 
    LEFT OUTER JOIN Users HGSQLUsers ON ImportExportBadFile.UserID = HGSQLUsers.ID
    INNER JOIN ImportExportSettings SETT ON ImportExportBadFile.ImportExportSettingID = SETT.ID  
    ORDER BY  ImportExportBadFile.DateTimeStamp DESC

It gives the error: "Incorrect syntax nearby". ". on the line:" Coalesce (UserName, '') HGSQLUsers.UserName "

If I write only UserName

He gives

"Ambiguous column name 'UserName'."

Reference.

+3


source to share


2 answers


Probably the column UserName

exists in both tables, you need to specify which table to take UserName

from as shown below

SELECT  ImportExportBadFile.ID, ImportExportSettingID,
    SETT.Name,
    ImportExportBadFile.UserID,
    Coalesce( HGSQLUsers.UserName  ,'') as "HGSQLUsers.UserName"
    FROM
    ImportExportBadFile 
    LEFT OUTER JOIN Users HGSQLUsers ON ImportExportBadFile.UserID = HGSQLUsers.ID
    INNER JOIN ImportExportSettings SETT ON ImportExportBadFile.ImportExportSettingID = SETT.ID
ORDER BY  ImportExportBadFile.DateTimeStamp DESC

The error "Incorrect syntax near '.'." at line :

comes from the alias HGSQLUsers.UserName

, you can use it but in quotas.



I also recommend that you use aliases for each table as shown below - more readable for me

SELECT  I.ID, I.ImportExportSettingID,
        SETT.Name,
        I.UserID,
       Coalesce( HGSQLUsers .UserName  ,'') as "HGSQLUsers.UserName"
FROM ImportExportBadFile I
LEFT OUTER JOIN Users HGSQLUsers ON I.UserID = HGSQLUsers.ID
INNER JOIN ImportExportSettings SETT ON I.ImportExportSettingID = SETT.ID  
ORDER BY  I.DateTimeStamp DESC
+3


source


This actually worked for me on trial and error:

SELECT  ImportExportBadFile.ID, ImportExportSettingID,
    SETT.Name,
    ImportExportBadFile.UserID,
    Coalesce(HGSQLUsers.UserName,'')
    FROM
    ImportExportBadFile 
    INNER JOIN ImportExportSettings SETT ON ImportExportBadFile.ImportExportSettingID = SETT.ID  
    LEFT OUTER JOIN Users HGSQLUsers ON ImportExportBadFile.UserID = HGSQLUsers.ID
    ORDER BY  ImportExportBadFile.DateTimeStamp DESC


but @Parado's answer is more specific and explanatory.

Thank.

+1


source






More articles:


All Articles