Member Avatar for rom.

After updating mysql from 4 to 5, I'm now getting this error:

Invalid SQL: SELECT HRS.Hrs AS Val, HRS.Job_ID, TS.Staff_ID, Staff.First_name, Staff.Last_name FROM Timesheets AS TS, Timesheet_hrs AS HRS LEFT OUTER JOIN Staff ON (TS.Staff_ID=Staff.ID) WHERE TS.ID=HRS.Timesheet_ID ORDER BY Staff.First_name, Staff.Last_name, HRS.Job_ID DESC:
1054 (Unknown column 'TS.Staff_ID' in 'on clause')

1054 is a known issue:
http://bugs.mysql.com/bug.php?id=13551

I've tried wrapping the FROM conditions in brackets, but it doesn't fix the problem entirely.
Since I'm a mysql noob, would anyone be kind enough to help translate the SELECT line into a working mysql5 command?

cheers

I'll update this as soon as I have a working fix

  • 1 Contributor
  • 1 Reply
  • 291 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by rom.
Member Avatar for rom.

Right, still not entirely sure how this works, but I'm told it needed to be intrusive, not extrusive..? Also, something about commas have a lower value than JOINs.. In anycase, heres a working version.

VERSION mySQL 4

	SELECT $val_select HRS.Job_ID, TS.Staff_ID, Staff.First_name, Staff.Last_name 
	FROM Timesheets AS TS, Timesheet_hrs AS HRS
	LEFT OUTER JOIN Staff 
	ON (TS.Staff_ID = Staff.ID) 
	WHERE TS.ID = HRS.Timesheet_ID
	ORDER BY Staff.First_name, Staff.Last_name, HRS.Job_ID DESC

VERSION mySQL 5

	SELECT $val_select HRS.Job_ID, TS.Staff_ID, Staff.First_name, Staff.Last_name 
	FROM Timesheets AS TS 
	JOIN Staff ON TS.Staff_ID = Staff.ID
	JOIN Timesheet_hrs AS HRS ON TS.ID = HRS.Timesheet_ID
	ORDER BY Staff.First_name, Staff.Last_name, HRS.Job_ID DESC
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.