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


-- 

* * 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