www.webdeveloper.com
Results 1 to 4 of 4

Thread: Complex 'Not Exists' Query - SQL Server 2005

  1. #1
    Join Date
    Sep 2003
    Posts
    94

    Complex 'Not Exists' Query - SQL Server 2005

    Hi! I am trying to find some results NOT in a resultset

    The query I am using to get the resultset:

    Code:
    SELECT     
         BD.id, 
         BD.a_id, 
         CONVERT(varchar, A.agent_id) + ' ' + A.fname + ' ' + A.lname AS name,   
         BD.b_id, 
         BD.agent_id, 
         BD.inactive, 
         BD.english, 
         BD.french, 
         BD.province
    FROM Univlicval_billing_digits AS BD INNER JOIN
         Univlicval_agents AS A ON BD.a_id = A.id
    WHERE
         (BD.b_id = 3) AND (BD.province = 'AB')
    This gives me 5 results.

    I want to get the rest of the agents that are not already assigned to the b_id AND province.

    I run this query:

    Code:
    SELECT DISTINCT id, agent_id, fname, lname, inactive
    FROM         Univlicval_agents AS A
    WHERE     (NOT EXISTS
                              (SELECT     a_id
                                FROM          Univlicval_billing_digits AS BD
                                WHERE      (A.id = a_id) AND (b_id = 3) AND (province = 'AB')))
    And get a complete resultset from agents (24 rows) where I should have 18 rows.

    I know I'm missing something simple, but I can't figure out what it is. Thanks in advance!

    Thanks in advance!

  2. #2
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Code:
    SELECT DISTINCT id, agent_id, fname, lname, inactive
    FROM         Univlicval_agents AS A
    WHERE     id NOT IN (
                              (SELECT     a_id
                                FROM          Univlicval_billing_digits AS BD
                                WHERE      (A.id = a_id) AND (b_id = 3) AND (province = 'AB')))
    Should do it.

    Except I just noticed - this probably won't work, as this is looking to select records where id's are not in a select that specifically looks for the id's you are not looking for.

    I think.

    Hmm...
    Last edited by WolfShade; 06-14-2011 at 02:05 PM.

  3. #3
    Join Date
    Sep 2003
    Posts
    94
    That is correct, that query brought back all 24 records.

  4. #4
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Someone told me that MSSQL 2005 will eventually truncate

    WHERE ? NOT IN ( ... )

    Any truth to that?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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



Recent Articles