I am trying to get the following functionality working which requries some SQL. The need is to be able to produce a list of general staff who are not on holiday or on other sessions on a given day. I have two sql statements that will give me the information that I need.
This will search the Holidays table and retrive the staff who are not on holiday.
Select StaffID from Staff as noneHolidayStaff where StaffID
not in
(
Select StaffID from StaffHolDays where StaffHolDays.HolDate = '2010-10-10'
);Gives:
+---------+
| StaffID |
+---------+
| 1 |
| 2 |
| 3 |
+---------+
The second section will give the information for staff who are not on a session.
Select StaffID from Staff As NotOnSession where StaffID
not in
(
Select StaffID from StaffEvent where StaffEvent.EventID
in
(
Select EventID from Event where SessionID
in
(
Select SessionID from Session where SessionDate = '2010-10-10'
)
)
);Gives
+---------+
| StaffID |
+---------+
| 3 |
| 4 |
+...........+
Both these items work. The reason why I have created a table alias (“noneHolidayStaff”,” NotOnSession” is so that I can then create a join between the two tables. This is all carried out within a stored Procedure in which I am thinking that if I use a Join statement it will produce a list of staff who are available such as the following diagram states:
+----------------------+
| Staff not on holiday |
+----------------------+
| 1 |
| 2 |
| 3 |
+----------------------+
3
+----------------------+
| StaffID |
+----------------------+
| 3 |
| 4 |
+......................+
The number displayed in the middle is the only one who is a match in both tables. This is then passed through to the Staff table to select the people who can attend the session.
I am having a problem with the Join statement that will do this sorting.
Select *, concat (StaffFName, ' ', StaffSName) as FullName from Staff
where StaffID
in
(
Select StaffID from Staff As NotOnSession where StaffID
not in
(
Select StaffID from StaffEvent where StaffEvent.EventID
in
(
Select EventID from Event where SessionID
in
(
Select SessionID from Session where SessionDate = '2010-10-10'
)
)
)
/* Would a join go here?*/
Select StaffID from Staff as noneHolidayStaff where StaffID
not in
(
Select StaffID from StaffHolDays where StaffHolDays.HolDate = '2010-10-10'
)
On noneHolidayStaff.StaffID = NotOnSession.StaffID
)
;This will be ran from a stored procedure and will '2010-10-10' will be replaced with a parameter.
I am confident in the two separate sql statements, I have ran them both separately.
The problem: It is merely the construction of the join with
the two Derived tables. Any help would be great.