Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007
    Posts
    19

    Unanswered: Select statement with sub-query not returning any rows

    I have the following SQL statement where I am trying to find SIGID rows in SIGNATURES that have no matching value in any fields in the B table. The B table's field holds an integer value that points to a signature in the SIGNATURES table.

    I have UNIONed all the selects together to give me a list (which it will do if I run it seperately).

    When I run this, I get back 0 rows found. There are rows that have no matching values and I should be returning some rows.

    All of the fields are integer fields but I have even tried convert(int,<field>) on all of them with no luck.

    Please help me understand what I am doing wrong.


    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    select distinct SIGID from signatures with(NOLOCK)
    where SIGID not in
    (select distinct FATHER_SIG from B with(NOLOCK) where event_year = 2007 and FATHER_SIG is not null
    and user_location <> 'central' and isactive = 'T' and NOTARY_1_SIG is not null UNION select distinct NOTARY_1_SIG from B with (NOLOCK)where event_year = 2007
    and user_location <> 'central' and isactive = 'T' and MOTHER_SIG is not null UNION select distinct MOTHER_SIG from B with (NOLOCK)where event_year = 2007
    and user_location <> 'central' and isactive = 'T' and NOTARY_2_SIG is not null UNION select distinct NOTARY_2_SIG from B with (NOLOCK)where event_year = 2007
    and user_location <> 'central' and isactive = 'T' and FATHER_WIT_1_SIG is not null UNION select distinct FATHER_WIT_1_SIG from B with (NOLOCK)where event_year = 2007
    and user_location <> 'central' and isactive = 'T' and MOTHER_WIT_1_SIG is not null UNION select distinct MOTHER_WIT_1_SIG from B with (NOLOCK)where event_year = 2007
    and user_location <> 'central' and isactive = 'T' and MOTHER_WIT_2_SIG is not null UNION select distinct MOTHER_WIT_2_SIG from B with (NOLOCK)where event_year = 2007
    and user_location <> 'central' and isactive = 'T' and FATHER_WIT_2_SIG is not null UNION select distinct FATHER_WIT_2_SIG from B with (NOLOCK)where event_year = 2007)
    Last edited by loquin; 08-28-07 at 16:07. Reason: Do NOT delete post, as this means it's unavailable for future reference by others with a similar issue...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm making a bit of a stretch here, but I think that what you want is:
    Code:
    SELECT DISTINCT SIGID
       FROM SIGNATURES AS A WITH(NOLOCK)
       WHERE NOT EXISTS (SELECT *
          FROM signatures AS b WITH (NOLOCK)
          WHERE  'central' <> b.user_location
             AND 'T' = b.isactive
             AND 2007 = b.event_year
             AND a.SIGID in (b.FATHER_SIG, b.MOTHER_SIG
    ,           b.NOTARY_1_SIG, b.NOTARY_2_SIG
    ,           b.FATHER_WIT_1_SIG, b.FATHER_WIT_2_SIG
    ,           b.MOTHER_WIT_1_SIG, b.MOTHER_WIT_2_SIG))
    Note that this smells very strongly of a table design that is not in first normal form, which usually makes queries like this a lot harder than they need to be!

    -PatP

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Btw, no need for DISTINCTs in your subquery, since UNION will remove duplicates anyhow.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •