Select statement with sub-query not returning any rows

data1025 · #1 (**permalink**) 08-27-07, 11:42

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)

Pat Phelan · #2 (**permalink**) 08-27-07, 13:02

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

Peter.Vanroose · #3 (**permalink**) 08-29-07, 14:35

Btw, no need for DISTINCTs in your subquery, since UNION will remove duplicates anyhow.

Internet Services

Web Development

Digital Marketing

Consumer Tech