CODEWITHSUNDEEP
mysqlsql
user3254294
user3254294

Reputation: 1

mySQL "Unknown column" error

  $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%'";

I am getting "Unknown column 'u1.userName' in 'on clause'". I don't understand as I am assigning an alias in my FROM clause. I am fairly new to SQL though. I did search for an answer but none of the answers I found seem to help.

Any advice is appreciated.

Thank in advance

Dee

Upvotes: 0

Views: 35

Answers (2)

Siyual
Siyual

Reputation: 16917

I'm assuming the problem is that you're referencing the first table in the On clause for the Join between the second table and tblEquipIssues.

You'll need to Join to each table:

...
FROM    tblEquipIssues  
JOIN    tblusers        AS u1   ON (tblEquipIssues.EnteredBy = u1.userName)
JOIN    tblusers        AS u2   ON (tblEquipIssues.FixedBy = u2.userName)
WHERE   ...

Upvotes: 0

cha
cha

Reputation: 10411

It happens because the ON clause sees only the two tables that are actually JOINed. When you use this syntax:

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

In the ON clause you will be able to refer to columns from u2 and tblEquipIssues

You need to rewrite your query, so that all tables are JOINed:

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

Upvotes: 2

Related Questions