[thelist] sql join statement

[Matt Warden]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Ken,

Ken Schaefer wrote:
> 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

Because you're using an inner join, this will not show employees
without a shift. I know the OP didn't say anything about desiring
this, but it is usually desirable to know (e.g., hey, Joe doesn't have
any shifts; so we can assign him this one), especially since it looks
like it's a shift schedule that is being generated (Larry might want
to know explicitly that he has no shifts). It's possible that the OP
would also want shifts that do not yet have people assigned, but I
will leave that unless it's requested.


SELECT
  a.UserFName,
  a.UserName,
  b.shiftDate,
  b.shiftStartTime,
  b.shiftEndTime
FROM
  User AS a
LEFT OUTER 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


- --
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFC0+o2AQ0d4HGyPE8RAhAQAJ4+suIJl5d8gRUafiJFpLkoEPwL9QCeLPxQ
clDutGN9UC0jU1W0xOgsxTY=
=c5Y/
-----END PGP SIGNATURE-----