I have 4 tables; Shifts, Employees, Locations, and Employee Types.
My Goal is to Select a group of shifts that fall within a date criteria and join all of the above tables. Here's what I came up with but it is obviously not working or I wouldn't be posting here :-(
Select * From Shifts LEFT JOIN Employees ON Employees.EmployeeID=Shifts.EmployeeID LEFT JOIN Location ON Location.LocationID=Shifts.LocationID LEFT JOIN EmployeeTypes ON EmployeeTypes.TypeID=Employees.EmployeeType Where Shifts.Begin < #16-Mar-2011 12:00:00 AM# And Shifts.End > #15-Mar-2011 12:00:00 AM# And (Shifts.Type = 2 OR Shifts.Type = 3)
The Location Table, may or may not have an entry in the table that corresponds with the Shifts.LocationID (ie: I want to support a location ID of 0 in the shifts table)
Some help with this SQL would really be appreciated!