[thelist] sql join statement

James Hardy evolt at weeb.biz
Wed Jul 13 05:20:48 CDT 2005


Mike D. Johnson wrote:
> When I use this sql statement:  
> 
> 	sql="SELECT user.userFname, user.userLname,
> shift.shiftStartDate, shift.shiftStartTime, shift.shiftEndTime FROM
> [user] LEFT JOIN "
> 
> 	sql=sql &"shiftUserLink ON user.userName=shiftUserLink.userName
> LEFT JOIN shift ON shiftUserLink.shiftID = shift.shiftID "
> 
> 	sql=sql &"ORDER BY user.userFname, user.userLname,
> shift.shiftStartDate, shift.shiftStartTime, shift.shiftEndTime"
> 
> I get an error that says 
> 
> Syntax error (missing operator) in query expression 'user.userName =
> shiftUserLink.userName LEFT JOIN shift ON shiftUserLink.shiftID =
> shift.shiftID'.
> 
> Also, where do I put in the variable for saleID?  I want to select only
> the shifts where shift.saleID=1.
> 
> 
Not 100% sure on Access DBs, I stopped using them a few years ago and 
moved on to MSSQL and MySQL so forgive me if my memory is a little fuzzy 
on all those little nuances. Try bracketing the first join ie add a "(" 
after "FROM" and a ")" before the second "LEFT JOIN" - it is possible 
that the JET engine is having difficulty optimising the query.

As for the where clause, Insert " WHERE shift.saleID=1 " directly before 
"ORDER BY "

have a look at http://www.w3schools.com/SQL/
they have a good reference as to the formats of all the basic queries.


More information about the thelist mailing list