SQL NOT IN question


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: SQL NOT IN question

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    SQL NOT IN question

    [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

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:SQL NOT IN question

    [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

  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:SQL NOT IN question

    [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

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:SQL NOT IN question

    [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.

  5. #5
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:SQL NOT IN question

    [Originally posted by Stephen McAllister]

    Thanks for the time and explanation.

    I will try this and see how we go.

    Stephen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center