mysql - MySQL的“未知列”错误

  $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相当陌生。我确实在寻找答案，但我发现所有答案似乎都没有帮助。

任何建议表示赞赏。

预先感谢

迪

最佳答案

发生这种情况是因为ON子句只能看到实际上已联接的两个表。使用此语法时：

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%'