[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