[thelist] sql join statement

Ken Schaefer Ken at adOpenStatic.com
Tue Jul 12 09:07:05 CDT 2005


The SQL statement would be like:

SELECT
  a.UserFName,
  a.UserName,
  b.shiftDate,
  b.shiftStartTime,
  b.shiftEndTime
FROM
  User AS a
INNER JOIN
(
    shiftUserLinkTable AS c
  INNER JOIN
    Shift AS b
  ON
   b.ShiftID = c.ShiftID
)
ON
	a.UserName = b.userName
ORDER BY
	a.UserFName,
	b.ShiftDate,
	b.ShiftStartTime,
	b.ShiftEndTime

(check the placement of ( ) in the SQL statement in the Access Query Builder)

You would use VBScript to format the output into the presentation you have
below.

Cheers
Ken
   
--
www.adOpenStatic.com/cs/blogs/ken/ 

: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Mike D. Johnson
: Sent: Tuesday, 12 July 2005 11:43 PM
: To: thelist at lists.evolt.org
: Subject: [thelist] sql join statement
: 
: 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
: --
: 
: * * 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