homepage Welcome to WebmasterWorld Guest from 54.161.192.130
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL Join Left Not Working
left join
compubasic



 
Msg#: 4202205 posted 3:02 pm on Sep 15, 2010 (gmt 0)

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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4202205 posted 3:44 pm on Sep 15, 2010 (gmt 0)

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

compubasic



 
Msg#: 4202205 posted 3:59 pm on Sep 15, 2010 (gmt 0)

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



 
Msg#: 4202205 posted 4:13 pm on Sep 15, 2010 (gmt 0)

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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4202205 posted 4:26 pm on Sep 15, 2010 (gmt 0)

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



 
Msg#: 4202205 posted 4:38 pm on Sep 15, 2010 (gmt 0)

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

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4202205 posted 4:39 pm on Sep 15, 2010 (gmt 0)

Cool!

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved