Professional Web Applications Themes

SELECT... WHERE... NOT IN(SELECT...) - Microsoft SQL / MS SQL Server

Hey Everybody. I am trying to run a query that selects all rows from 1 table that do not have a corresponding id in another table. Left outer join doesn't work, and querying a "NOT IN (subquery)" isn't working either for some reason or another. Anyone heard of any bugs/fixes/etc. Any help is greatly appreciated. Thanks alot....

  1. #1

    Default SELECT... WHERE... NOT IN(SELECT...)

    Hey Everybody. I am trying to run a query that selects all
    rows from 1 table that do not have a corresponding id in
    another table. Left outer join doesn't work, and querying
    a "NOT IN (subquery)" isn't working either for some reason
    or another. Anyone heard of any bugs/fixes/etc. Any help
    is greatly appreciated. Thanks alot.
    Ian Guest

  2. #2

    Default SELECT... WHERE... NOT IN(SELECT...)

    Pls post table definition , sameplae data and the query
    that you are trying to run.For selecting rows which are in
    one table where the corresponding id doesn't exusts in the
    other table query as given below

    Select *
    from Table1 a
    where Not Exists ( select 1 from Table2 b where a.Id =
    b.Id)

    or

    Select *
    from TableA a
    left outer join Tableb b on ( a.Id = b.Id)
    where b.Id is Null

    HTH
    Srinivas Sampangi



     
    all 
    reason 
    sampangi Guest

  3. #3

    Default Re: SELECT... WHERE... NOT IN(SELECT...)

    Ian,

    It helps a lot if you explain what "not working" means.
    The most common reason things like this yield unexpected
    results (usually no rows are returned) has to do with NULL
    values.

    My guess is that your subquery returns one or more NULL
    values, which causes NOT IN (subquery) to be FALSE or
    UNKNOWN, but never TRUE. As for the left join, maybe
    you are comparing NULLs using =, or using legacy *=
    syntax, or something else. If you show us your query or a simplified
    version of it, and your table definitions, it will help.

    The only bug I can think of like this has to do with an
    outer join on a view with a constant computed column.

    -- Steve Kass
    -- Drew University
    -- Ref: D55EC589-50F1-49BE-840B-99A49E4611CB

    Ian wrote:
     

    Steve Guest

  4. #4

    Default Re: SELECT... WHERE... NOT IN(SELECT...)

    Usually I use

    SELECT <A.fields>
    FROM A
    LEFT OUTER JOIN B
    ON A.ID = B.A_ID
    WHERE B.A_ID IS NULL

    Never saw when this query "didn't work" really

    "Ian" <com> wrote in message
    news:460301c35647$1b47e870$gbl... 


    Alex Guest

Similar Threads

  1. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  2. Replies: 0
    Last Post: September 24th, 03:24 AM
  3. Replies: 0
    Last Post: September 11th, 11:26 AM
  4. Replies: 0
    Last Post: September 11th, 12:19 AM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139