如何在查询中添加另一个JOIN语句

Question

我有一个.aspx页面，上面有一个gridview项目。gridview最初显示正确，查询如下：

CommandText = "SELECT AnalyticsHub_Requests.*, 
    AnalyticsHub_StatusCodes.ShortDescription AS StatusText 
    FROM AnalyticsHub_Requests 
    LEFT JOIN AnalyticsHub_StatusCodes 
    ON (AnalyticsHub_Requests.StatusId=AnalyticsHub_StatusCodes.Id) 
    WHERE AnalyticsHub_Requests.StatusId IN (4,6)";

我想显示另一个表中的更多信息，以便列出分配给请求的人员。我试着这样做：

CommandText = "SELECT AnalyticsHub_Requests.*, AnalyticsHub_StatusCodes.ShortDescription 
               AS StatusText 
               FROM AnalyticsHub_Requests 
               LEFT JOIN AnalyticsHub_StatusCodes 
               ON (AnalyticsHub_Requests.StatusId=AnalyticsHub_StatusCodes.Id) 
               WHERE AnalyticsHub_Requests.StatusId IN (4,6) , AnalyticsHub_Assignees.* 
              LEFT JOIN AnalyticsHub_Requests 
              ON (AnalyticsHub_Assignees.AssigneeId=AnalyticsHub_Requests.AssigneeId) 
              WHERE AnalyticsHub_Requests.AssigneeId IN (1,2,3,4,5,6)";

当我试图让它显示受让人信息时，什么都不会显示。我如何让它在表中显示受让人的信息？

Answer 1

您的SQL语法有几个地方是错误的。我从您的查询中了解到，您希望AnalyticsHub_Requests到LEFT JOIN带有AnalyticsHub_StatusCodes，AnalyticsHub_Assignees到LEFT JOIN带有AnalyticsHub_Requests。你可以通过这个SQL来完成。

SELECT AnalyticsHub_Requests.*, AnalyticsHub_StatusCodes.ShortDescription AS StatusText, 
    AnalyticsHub_Assignees.* 
FROM 
    AnalyticsHub_Requests 
LEFT JOIN 
    AnalyticsHub_StatusCodes 
        ON (AnalyticsHub_Requests.StatusId=AnalyticsHub_StatusCodes.Id) 
RIGHT JOIN
    AnalyticsHub_Assignees
        ON (AnalyticsHub_Assignees.AssigneeId=AnalyticsHub_Requests.AssigneeId) 
WHERE  AnalyticsHub_Requests.StatusId IN (4,6)
    AND
AnalyticsHub_Requests.AssigneeId IN (1,2,3,4,5,6)