Professional Web Applications Themes

Selecting based on an Inclusive List - Microsoft SQL / MS SQL Server

This is probably simple, but I cannot figure how to do this. I'm wanting to select all records in column1 that have ALL of the values in a list that exist in column2. A sample of the table is below. (simplified) So for example, I want to select all of the ControlNum values that contain an Oid value of 130, 131 and 132, but not any ControlNum that only has one or two of these three values. So from this sample, I would want to get ControlNum 3080238901 and 6771105094 returned with the query. Thanks for help. ControlNum Oid ----------------- ...

  1. #1

    Default Selecting based on an Inclusive List

    This is probably simple, but I cannot figure how to do
    this. I'm wanting to select all records in column1 that
    have ALL of the values in a list that exist in column2. A
    sample of the table is below. (simplified)

    So for example, I want to select all of the ControlNum
    values that contain an Oid value of 130, 131 and 132, but
    not any ControlNum that only has one or two of these three
    values. So from this sample, I would want to get
    ControlNum 3080238901 and 6771105094 returned with the
    query. Thanks for help.


    ControlNum Oid
    ----------------- ----
    3080238901 130
    3080238901 132
    3080238901 131
    3080469461 131
    3080469461 132
    4921789908 132
    5080595114 131
    5080993021 132
    5081043714 131
    6771105094 131
    6771105094 132
    6771105094 130

    Scott Guest

  2. #2

    Default Re: Selecting based on an Inclusive List

    Assuming the same Oid does not repeat for a ControlNum, try this

    select distinct ControlNum from test_q t1
    where exists (select * from test_q t2 where t2.controlnum = t1.controlnum
    group by controlnum having count(*) >2)


    "Scott" <com> wrote in message
    news:014701c34af7$fbb4e620$gbl... 


    Prasad Guest

  3. #3

    Default Re: Selecting based on an Inclusive List

    Try the following,

    select distinct ControlNum from test_q t1
    where exists (select * from test_q t2
    where t2.controlnum = t1.controlnum
    and Oid in (130,131,132)
    group by controlnum having count(distinct Oid) >2)

    "Scott" <com> wrote in message
    news:01b401c34afd$a6ee4400$gbl... 
    > try this 
    > = t1.controlnum [/ref]
    > column2. A [/ref]
    > but [/ref]
    > three 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Prasad Guest

  4. #4

    Default Re: Selecting based on an Inclusive List

    Prasad,

    Outstanding. This works perfectly. Thank you very much.

    Scott 
    Oid) >2) 

    Scott Guest

Similar Threads

  1. selecting based on matches on multiple rows?
    By Tim Smith in forum MySQL
    Replies: 1
    Last Post: May 31st, 01:32 AM
  2. Selecting Records based on DB column with list
    By craigkaminsky in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 4th, 07:41 PM
  3. Selecting data based on user input
    By Svein Olav Steinmo in forum ASP Database
    Replies: 3
    Last Post: September 23rd, 09:17 AM
  4. selecting based on a month in a date
    By Creative Solutions New Media in forum PHP Development
    Replies: 0
    Last Post: August 28th, 10:19 PM
  5. Selecting records based on multiple values
    By Jacob in forum ASP Database
    Replies: 1
    Last Post: July 7th, 03:56 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