0

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.

2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by taylby
2

Hi

Supposing your both queries select staffids which are not on holidays OR in
sessions you should select staffids which are in both result sets:

select * from staff 
  where staffid in (your select not on holidays )
    AND staffid in (your select not in sessions);

-- tesu

0

Thank you for this. it was the and statement that was confusing me; I had already tried an and; but yet I hadn't got the

StaffID in

to follow it. which means that I was getting the standard subquery contains more than one row type error.

--Taylby

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.