[thelist] sql join statement
Mike D. Johnson
mdjohnson at bcbe.org
Tue Jul 12 14:55:34 CDT 2005
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.
-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of James Hardy
Sent: Tuesday, July 12, 2005 9:18 AM
To: thelist at lists.evolt.org
Subject: Re: [thelist] sql join statement
Mike D. Johnson wrote:
> I am trying to select data from three different tables and can't get
it
> to work. I have three tables with these fields:
>
> user table
> userName
> userLname
> userFname
>
> shift table
> shiftID
> shiftDate
> shiftStartTime
> shiftEndTime
> saleID
>
> shiftUserLinkTable
> shiftID
> userName
>
>
> I need to select the shifts users are working at this sale and display
> it like this:
>
> UserFname userLname
> shiftDate shiftStartTime shiftEndTime
>
> Bob Smith
> 7/15/2005 8:00 AM - 9:00 AM
> 7/15/2005 10:00 AM - 11:00 AM
>
> Larry Jones
> 7/15/2005 10:00 AM - 11:00 AM
> 7/15/2005 12:00 PM - 1:00 PM
>
> I'm using MS Access and ASP (vbScript) for this.
>
> I need help constructing the sql statement to select this information.
> I'd also appreciate any links to sites with more info on doing this so
I
> can figure it out on my own next time.
>
> Thanks,
> Mike
I've done things like this a few times. For your case
the query would be:
"SELECT user.userFname, user.userLname, shift.shiftDate
shift.shiftStartTime, shift.shiftEndTime FROM user LEFT JOIN
shiftUserLinkTable ON user.userName = shiftUserLinkTable.userName LEFT
JOIN shift ON shiftUserLinkTable.shiftID = shift.shiftID ORDER BY
user.userFname, user.userLname, shift.shiftDate, shift.shiftStartTime,
shift.shiftEndTime"
then in ASP something after openning the recodset something like this:
currentName = ""
inTable = false
Do While Not rsShifts.EOF
if currentName <> rsShifts("userFname")&" "&rsShifts("userLname")
then
currentName = rsShifts("userFname")&" "&rsShifts("userLname")
If inTable Then Response.Write "</table>"
Response.Write "<h2>"¤tName&"<h2>"
Response.Write "<table>"
inTable=true
end if
response.write "<tr><td>" & rsShifts("shiftDate") & "</td><td>" & _
rsShifts("shiftStartTime") & "</td><td>" & _
rsShifts("shiftEndTime") & "</td></tr>"
'or whatever formatting you want
rsShift.moveNext
Loop
If inTable Then Response.Write "</table>"
think this should work.
--
* * Please support the community that supports you. * *
http://evolt.org/help_support_evolt/
For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !
More information about the thelist
mailing list