Professional Web Applications Themes

Select rows where other related rows don't exist - Microsoft SQL / MS SQL Server

I would like to select rows from a table where another related row in the same table doesn't exist and the relation key is more than 1 column. Example: For the table, keycol1 keycol2 datacol1 -------------------------------------- 1 1 B 1 1 I 2 2 B 3 3 B 3 3 I 4 4 B 4 4 I 5 5 B Select the 'B' rows which do not have the corresponding 'I' row for the key pair "keycol1,keycol2" So we would get back keycol1 keycol2 datacol1 -------------------------------------- 2 2 B 5 5 B Thanks for any suggestions, Dave...

  1. #1

    Default Select rows where other related rows don't exist

    I would like to select rows from a table where another related row in the
    same table doesn't exist and the relation key is more than 1 column.

    Example:

    For the table,

    keycol1 keycol2 datacol1
    --------------------------------------
    1 1 B
    1 1 I
    2 2 B
    3 3 B
    3 3 I
    4 4 B
    4 4 I
    5 5 B

    Select the 'B' rows which do not have the corresponding 'I' row for the key
    pair "keycol1,keycol2"

    So we would get back

    keycol1 keycol2 datacol1
    --------------------------------------
    2 2 B
    5 5 B


    Thanks for any suggestions,

    Dave


    Dmand Guest

  2. #2

    Default Re: Select rows where other related rows don't exist

    Works a treat!

    "Falik Sher" <falikshotmail.com> wrote in message
    news:%23o7Y5oFQDHA.1748TK2MSFTNGP11.phx.gbl...
    > Try this
    >
    > select t1.keycol1, t1.keycol2 from test1 t1 group by t1.keycol1,
    t1.keycol2
    > having count(datacol1)=1
    >
    > HTH
    > Falik
    >
    > "Dmand" <dandersonnospamimxsoftware.com> wrote in message
    > news:%23oErHVFQDHA.2768tk2msftngp13.phx.gbl...
    > > I would like to select rows from a table where another related row in
    the
    > > same table doesn't exist and the relation key is more than 1 column.
    > >
    > > Example:
    > >
    > > For the table,
    > >
    > > keycol1 keycol2 datacol1
    > > --------------------------------------
    > > 1 1 B
    > > 1 1 I
    > > 2 2 B
    > > 3 3 B
    > > 3 3 I
    > > 4 4 B
    > > 4 4 I
    > > 5 5 B
    > >
    > > Select the 'B' rows which do not have the corresponding 'I' row for the
    > key
    > > pair "keycol1,keycol2"
    > >
    > > So we would get back
    > >
    > > keycol1 keycol2 datacol1
    > > --------------------------------------
    > > 2 2 B
    > > 5 5 B
    > >
    > >
    > > Thanks for any suggestions,
    > >
    > > Dave
    > >
    > >
    >
    >

    Dmand Guest

  3. #3

    Default Re: Select rows where other related rows don't exist

    SELECT *
    FROM Sometable AS S1
    WHERE datacol1='B'
    AND NOT EXISTS
    (SELECT *
    FROM Sometable
    WHERE keycol1 = S1.keycol1 AND keycol2 = S1.keycol2 AND datacol1='I')

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

Similar Threads

  1. Select Multiple Rows within CFGRID?
    By roninDLC in forum Coldfusion Flash Integration
    Replies: 14
    Last Post: January 10th, 02:47 AM
  2. Select datagrid rows with key-up and key-down?
    By Mervin Williams in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: October 20th, 09:24 AM
  3. How to select first 50 rows?
    By miyaki in forum Informix
    Replies: 4
    Last Post: October 29th, 02:01 PM
  4. limiting rows returned in a sql select
    By Lennart Jonsson in forum IBM DB2
    Replies: 2
    Last Post: August 26th, 03:41 AM
  5. #22597 [Com]: PHP can't select random rows from MySql
    By justin at visunet dot ie in forum PHP Development
    Replies: 0
    Last Post: July 29th, 03:57 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