[thelist] sql join statement
James Hardy
evolt at weeb.biz
Tue Jul 12 09:17:34 CDT 2005
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.
More information about the thelist
mailing list