NOT IN Query Returns No Results

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

Date: Fri, 17 Sep 2004 10:12:29 -0700

Thanks Vishal. I guess I just have to remember use the IS
NOT NULL clause in the subquery or use the NOT EXISTS
>-----Original Message-----
>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: Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)
    ... > Is it possible to in Access SQL or SQLServer SQL to have a Compound SQL ... > subquery, but maybe there is a simple syntax error. ... I need the PK's in the cross tab query to ... > This WHERE has the same IN Clause as J1T4. ...
    (microsoft.public.access.queries)
  • Re: query wont run in access 97
    ... It returns a syntax error FROM Clause. ... A Jet subquery used in a FROM clause will always ... it is wrapped in brackets with an ending period ... or the Jet query parser will choke on them. ...
    (microsoft.public.access.queries)
  • Re: Help with derived table SQL statement in Access
    ... SELECT expression on the FROM clause, but that is just that, a table ... You can use three forms of syntax to create a subquery: ... You can also use table name aliases in a subquery to refer to tables ... than the average salary of all employees having the same job title. ...
    (microsoft.public.access.queries)
  • 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: subquery
    ... Generatrice ON [transport de generatrice].Nom = Generatrice.Nom) INNER ... SELECT clause, ... You can "correlate" the subquery back to the main ... on a field in the main query. ...
    (microsoft.public.access.queries)