“。”附近的语法不正确使用Coalesce时

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

我想加入三张桌子，我成功加入了两张桌子。

This Worked (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 Users Table
UserName属于Users Table

Now, when 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 Error : "Incorrect syntax near '.'." at line : "Coalesce(UserName,'') HGSQLUsers.UserName"

它给出了错误：“'。'附近的语法不正确。” at line：“Coalesce（UserName，''）HGSQLUsers.UserName”

If I write only UserName It gives

如果我只写UserName它给出

"Ambiguous column name 'UserName'."

“不明确的列名'UserName'。”

Help.

帮帮我。

2 个解决方案

#1

Probably UserName column exists in both tables, you need to specify from which table do want to take UserName as below

可能两个表中都存在UserName列，您需要指定从哪个表中获取UserName，如下所示

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

Error "Incorrect syntax near '.'." at line : comes from alias HGSQLUsers.UserName you can use it but in quotas.

错误“''附近的语法不正确。'。” at line：来自别名HGSQLUsers.UserName，您可以在配额中使用它。

I also recommend you to use aliases for every table as below - is 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

#2

Actually this worked for me in 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 explaining.

但@Parado的答案更具体和解释。

Thank you.

谢谢。

#1