SQL NOT IN question

[anonymous]

[Originally posted by Stephen McAllister]

Hi ALl,

The story:

Writing an app that searches staff who have a roster set up. There are a series of filters that I haveÿ set up that work individually but do not work as a group. The user can apply more than one filter at a time so the filters need to work as a group.

The problem:

Each group is filtered by a staff_id NOT IN ( SELECT staff_id FROM ....). The query is then executed. This means that some staff may be in one of the filters but not in the other. It does not work at the moment and I am not sure how to write the query so that the results are accurate.

If anyone understands what I am trying to achieve here (can they tell me... na... just kidding) and can help then please help.

Many thanks


Stephen

[anonymous]

[Originally posted by ada]

see whether i understand you or not.

What you want to do is to combine two or more filters together.

Suppose:
ÿ ÿ ÿ ÿ Filter1, select staff_id from group1
ÿ ÿ ÿ ÿ Filter2, select staff_id from group2

you need a filter to combine the above
ÿ ÿ ÿ ÿ Filter3, select staff_id from group1 UNION select staff_id from group2

To get a group not in the two above groups, query would be:
select staff_id not in Filter3

[anonymous]

[Originally posted by Stephen McAllister]

Hi.

Thanks for the help

I have managed to get the query working but the results are incorrect. The main reason for this is that one filter may include a staff member but the nest filter may disclude the same staff member which means that they then should not show. Is there a way to set up the query so that the list only shows staff that pass through all filters?

I guess a big AND section is needed.

Thanks

Stephen

[anonymous]

[Originally posted by ada]

while use UNION will combine the filters together.
e.g.

Filter5 = Filter1 UNION Filter2 UNION Filter3 UNION FIlter4

Then Filter5 will have all the staff members ofÿ 1, 2, 3, 4.

Select StaffId from Staff_Table where StaffId NOT IN Filter5, the result will remove all the members of 1, 2, 3, 4; no matter it exist in 1 or 2 or 3 or 4.

[anonymous]

[Originally posted by Stephen McAllister]

Thanks for the time and explanation.

I will try this and see how we go.

Stephen