[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>"&currentName&"<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