[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