mySQL "Unknown column"错误

Question

  $queryChart ="SELECT CONCAT(u1.adGivenName,' ', u1.adSurName) AS EnteredBy, CONCAT(u2.adGivenName,' ', u2.adSurName) AS FixedBy, tblEquipIssues.Floor, tblEquipIssues.Location, tblEquipIssues.LocationNum, tblEquipIssues.EquipType, tblEquipIssues.UnitNo, tblEquipIssues.SendTo, tblEquipIssues.IssueDate, tblEquipIssues.RespondDate, tblEquipIssues.FixedDate, tblEquipIssues.CloseTicket
    FROM tblusers AS u1, tblusers AS u2 
    INNER JOIN tblEquipIssues
    ON (tblEquipIssues.EnteredBy = u1.userName) AND (tblEquipIssues.FixedBy = u2.userName)
    WHERE tblEquipIssues.Location LIKE '%$Location%' AND
      tblEquipIssues.LocationNum LIKE '%$LocationNum%' AND
      tblEquipIssues.EquipType LIKE '%$EquipType%' AND
      tblEquipIssues.UnitNo LIKE '%$EquipNum%' AND
      (tblEquipIssues.IssueDate >= '$FromIssueDate' OR '$FromIssueDate' = '') AND 
      (tblEquipIssues.IssueDate <= '$ToIssueDate' OR '$ToIssueDate' = '') AND
      (tblEquipIssues.FixedDate >= '$FromFixedDate' OR '$FromFixedDate' = '') AND 
      (tblEquipIssues.FixedDate <= '$ToFixedDate' OR '$ToFixedDate' = '') AND
      tblEquipIssues.EnteredBy LIKE '%$EnteredBy%' AND
      tblEquipIssues.tblEquipIssuesID LIKE '%$TicketNum%' AND
      tblEquipIssues.FixedBy LIKE '%$Assigned%'";

我收到“‘on 子句’中的未知列‘u1.userName’”。我不明白，因为我在 FROM 子句中分配了别名。不过我对 SQL 还很陌生。我确实寻找了答案，但我找到的答案似乎都没有帮助。

如有任何建议，我们将不胜感激。

提前致谢

迪伊

Answer 1

发生这种情况是因为 ON 子句只看到实际 JOIN 的两个表。当您使用此语法时:

SELECT ....
FROM tblusers AS u1, tblusers AS u2 
INNER JOIN tblEquipIssues
ON ....

在 ON 子句中，您将能够引用 u2 和 tblEquipIssues 中的列

您需要重写查询，以便将所有表连接起来:

SELECT CONCAT(u1.adGivenName,' ', u1.adSurName) AS EnteredBy, 
CONCAT(u2.adGivenName,' ', u2.adSurName) AS FixedBy, tblEquipIssues.Floor, 
tblEquipIssues.Location, tblEquipIssues.LocationNum, tblEquipIssues.EquipType, 
tblEquipIssues.UnitNo, tblEquipIssues.SendTo, tblEquipIssues.IssueDate, 
tblEquipIssues.RespondDate, tblEquipIssues.FixedDate, tblEquipIssues.CloseTicket
FROM tblEquipIssues
INNER JOIN tblusers AS u1 ON tblEquipIssues.EnteredBy = u1.userName
INNER JOIN tblusers AS u2 ON tblEquipIssues.FixedBy = u2.userName
WHERE tblEquipIssues.Location LIKE '%$Location%' AND
  tblEquipIssues.LocationNum LIKE '%$LocationNum%' AND
  tblEquipIssues.EquipType LIKE '%$EquipType%' AND
  tblEquipIssues.UnitNo LIKE '%$EquipNum%' AND
  (tblEquipIssues.IssueDate >= '$FromIssueDate' OR '$FromIssueDate' = '') AND 
  (tblEquipIssues.IssueDate <= '$ToIssueDate' OR '$ToIssueDate' = '') AND
  (tblEquipIssues.FixedDate >= '$FromFixedDate' OR '$FromFixedDate' = '') AND 
  (tblEquipIssues.FixedDate <= '$ToFixedDate' OR '$ToFixedDate' = '') AND
  tblEquipIssues.EnteredBy LIKE '%$EnteredBy%' AND
  tblEquipIssues.tblEquipIssuesID LIKE '%$TicketNum%' AND
  tblEquipIssues.FixedBy LIKE '%$Assigned%'