Professional Web Applications Themes

Not in - Microsoft SQL / MS SQL Server

I have the following query where I am checking type. INSERT type(type,[id]) SELECT DISTINCT type,[id] FROM main WHERE type NOT IN (SELECT type FROM type) How do I need to modified a query if I need to check 3 parameters: type,description,flag?...

  1. #1

    Default Not in

    I have the following query where I am checking type.
    INSERT type(type,[id])
    SELECT DISTINCT type,[id]
    FROM main WHERE type
    NOT IN (SELECT type FROM type)

    How do I need to modified a query if I need to check 3
    parameters: type,description,flag?

    sardinka Guest

  2. #2

    Default Re: Not in

    sardinka wrote: 

    You need to give us a little more to go on. What exactly do you want to
    check about the 3 parameters? Are there description and flag tables similar
    to the type table? If you want to return records from main where the
    parameters can't be found in the corresponding tables (description and
    flag), then you could simply add two more NOT IN criteria ...

    Bob Barrows


    Bob Guest

  3. #3

    Default Re: Not in

    Hi Sardinka,

    Use NOT EXISTS or an OUTER JOIN:

    INSERT type(type,[id])
    SELECT DISTINCT type,[id]
    FROM main WHERE NOT EXISTS
    (SELECT NULL FROM type t
    WHERE t.type = main.type AND t.description = main.description AND t.flag
    = main.flag)

    INSERT type(type,[id])
    SELECT DISTINCT m.type,m.[id]
    FROM main m
    LEFT OUTER JOIN type t
    ON t.type = m.type AND t.description = m.description AND t.flag = m.flag
    WHERE t.type IS NULL


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "sardinka" <com> wrote in message
    news:096901c35dba$426680e0$gbl... 


    Jacco Guest

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