NOT IN Query Returns No Results

From: Baner (banerpr_at_discussions.microsoft.com)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 09:14:58 -0700

When running the following query and subquery with a NOT
IN clause, I'm getting no results with SQL Server 2000
SP3. Has anyone seen this before ?
=======================================
select * from T_TEST_PB_091704
where acct_or_lvl_nm not in (
     select distinct PAR_MEMB_ALIAS_TXT CACCT_OR_LVL_NM
     from t_temp_acct_hier_rap_new3
     )

There are 6 values in table T_TEST_PB_091704. 4 of these
exist in table t_temp_acct_hier_rap_new3.
I get the right results if:
a) I hard code the select list in the subquery by:
    select * from T_TEST_PB_091704
    where acct_or_lvl_nm not in (
          select distinct PAR_MEMB_ALIAS_TXT
          from t_temp_acct_hier_rap_new3
          where par_memb_alias_txt in (
                      'BOOK_BALANCE_AB',
                      'COMML_LOANS_AB',
                      'COMML_R_E_AB',
                      'MTG_BCK_SEC_AB',
                      'MTG_SVC_RHTS_AB',
                      'FLOAT_BALANCE_AB' )
         )

or, if I use T-SQL with an outer join:
 select DISTINCT a.acct_or_lvl_nm
 from dbo.T_TEST_PB_091704 a LEFT OUTER join
      dbo.t_temp_acct_hier_rap_new3 b
 ON a.acct_or_lvl_nm=b.par_memb_alias_txt
 WHERE b.Par_Memb_Alias_Txt is null

  --------
  MTG_BCK_SEC_AB
  MTG_SVC_RHTS_AB

or, if I use a NOT EXISTS clause:
  select * from T_TEST_PB_091704 a
  where NOT EXISTS
      ( select distinct PAR_MEMB_ALIAS_TXT CACCT_OR_LVL_NM
        from t_temp_acct_hier_rap_new3 b
        where a.acct_or_lvl_nm=b.par_memb_alias_txt )

  --------
  MTG_BCK_SEC_AB
  MTG_SVC_RHTS_AB

Thanks in advance
Baner

Relevant Pages

  • Re: update 2 fields problem
    ... as the SQL Server optimizer will usually generate the ... This changes if the subquery has to be duplicated. ... order in which matches are found depends on the execution plan, ... still be affected by the UPDATE statement and the new values for Column1 ...
    (microsoft.public.sqlserver.programming)
  • Re: Insert via another tables columns
    ... The UPDATE with a SET (SELECT subquery) also requires that col_a1 and col_a2 ... UPDATE clause, beside the subquery in the SET clause. ... Earlier version of FoxPro need to use xbase REPLACE command or a mix of SQL ... I try the same in SQL Server and works great. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Help needed with subqueries.
    ... TOP 100 Percent with an ORDER BY clause is meaningless, ... The set that is returned by the subquery is treated like a view ... SQL Server MVP ... TOP 100 percent is needed if the subquery contains an ORDER BY clause. ...
    (microsoft.public.sqlserver.programming)
  • Incorrect behavior in NOT IN subquery with OPENXML
    ... In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a subquery. ... I've tried to simplify this problem and I've ended up with the sql script ...
    (microsoft.public.sqlserver.xml)
  • Re: Subqueries with parameters
    ... > Crystal Reports. ... I am using SQL Server so I can do it with a stored ... > clauses or to put parameters on both the subquery and the main query. ... > Data Management Solutions LLC ...
    (microsoft.public.vb.crystal)