#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    2
    Rep Power
    0

    Loop databases and select distinct names when criteria not in database


    I have a list of databases I need to loop through. I need to pull out all the databases that do not contain a certain type (red in example).

    database Type

    1 Red
    1 Blue
    1 Green
    2 Red
    3 Blue
    4 Blue
    5 Green

    Therefore the query would show me databases 3,4,5 do not contain red. Although database 1 has other colours, it wouldn't show as it has red.

    Hope this makes sense, I haven't started the code yet.
    Last edited by nickmaca10; October 29th, 2013 at 08:47 AM. Reason: Solved
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Code:
    select database
      from listOfDatabases
     group
        by database
    having sum(case when type = 'Red' then 1 else 0 end) = 0

    Comments on this post

    • nickmaca10 agrees : Quick, easy to understand and helpful
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    2
    Rep Power
    0
    Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo