MySQL Join Left Not Working

left join

[compubasic]

I have a query to bring up records for a job log of IT work performed for a specified client. Part of the information I would like the query to gather is the name of office the work is associated with. In the joblog table itself the office is refereed to by and ID number which is the PK of another table, clientlocations. On some job entries in the joblog table, the ID number for the office is specified by a 0, denoting that the work performed was remote support. When attempting to look up the associated office name from clientlocations with PK 0, the whole resulting row is dropped because there is no PK entry of value 0 in the clientlocations table.

The following query pulls up all of the information I need EXCEPT for the office name as specified in the clientlocations table.

SELECT joblog.joblogJobID,
joblog.joblogPerformedOn,
joblog.joblogWorkDescription,
joblog.joblogTime,
joblog.joblogTravelCost,
joblog.joblogLaborCost,
joblog.joblogJobTotal,
joblog.joblogOfficeID,
billingclasses.billingclassesType,
users.userName
FROM joblog, billingclasses, users
WHERE joblog.joblogClientID=5 AND billingclasses.billingclassesID=joblog.joblogBillingClass AND
users.userID=joblog.joblogEngineerID


I modified the query with a LEFT JOIN:

SELECT joblog.joblogJobID,
joblog.joblogPerformedOn,
joblog.joblogWorkDescription,
joblog.joblogTime,
joblog.joblogTravelCost,
joblog.joblogLaborCost,
joblog.joblogJobTotal,
joblog.joblogOfficeID,
billingclasses.billingclassesType,
users.userName
FROM joblog, billingclasses, users
WHERE joblog.joblogClientID=5 AND billingclasses.billingclassesID=joblog.joblogBillingClass AND
users.userID=joblog.joblogEngineerID
LEFT JOIN clientlocations ON
clientlocations.clientlocationsOfficeID=joblog.joblogOfficeID


and I receive the following error message:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN clientlocations ON clientlocations.clientlocationsOfficeID=joblog.jobl' at line 1

Can't figure out what I am doing wrong, it seemed simple enough...

Any help? Thanks!

[LifeinAsia]

You need to put the JOIN in the FROM section of your query. You have it in the WHERE section.

[compubasic]

Can you please reformat my query to show me how it should be done. I have just tried and am now receiving this general error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE joblog.joblogClientID=$clientID AND billingclasses.billingclassesID=joblog' at line 1


New query:

SELECT joblog.joblogJobID, joblog.joblogPerformedOn, joblog.joblogWorkDescription, joblog.joblogTime, joblog.joblogTravelCost, joblog.joblogLaborCost, joblog.joblogJobTotal, joblog.joblogOfficeID, billingclasses.billingclassesType, users.userName FROM joblog, billingclasses, users LEFT JOIN clientlocations ON clientlocations.clientlocationsOffice WHERE clientlocations.clientlocationsOfficeID=joblog.joblogOfficeID WHERE joblog.joblogClientID=$clientID AND billingclasses.billingclassesID=joblog.joblogBillingClass AND users.userID=joblog.joblogEngineerID

[compubasic]

My apologies, that query wasn't working....

This query does return results, but it's still dropping the result rows where the joblogOfficeID=0. Here is the current query I have which returns (not all) results

SELECT joblog.joblogJobID, joblog.joblogPerformedOn, joblog.joblogWorkDescription, joblog.joblogTime, joblog.joblogTravelCost, joblog.joblogLaborCost, joblog.joblogJobTotal, joblog.joblogOfficeID, billingclasses.billingclassesType, users.userName, clientlocations.clientlocationsOffice FROM joblog, billingclasses, users LEFT JOIN clientlocations ON clientlocations.clientlocationsOfficeID WHERE joblog.joblogClientID=5 AND clientlocations.clientlocationsOfficeID=joblog.joblogOfficeID AND billingclasses.billingclassesID=joblog.joblogBillingClass AND users.userID=joblog.joblogEngineerID

[LifeinAsia]

The proper syntax for a LEFT JOIN is:
FROM TableA LEFT JOIN TableB ON TableA.SomeField=TableB.SomeField

(The "connection" between the two tables is where TableA.SomeField=TableB.SomeField.)

[compubasic]

Thanks! You've finally made it clear to me, I now have it working.

[LifeinAsia]

Cool!

Oh, and a hearty welcome to WebmasterWorld [webmasterworld.com]! :)