Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    2

    Unanswered: Select All Rows Where All Criteria Is Met Across Multiple Records

    Hey there,

    I'll try to make this easy to understand cause apparently I'm a moron. I have a table that has two columns. Column 1 is UID and column 2 is Attribute. Lets say I have 10 records for 5 different UIDs. Some of the users have more than one record meaning they have more than one attribute. (so far, so good)

    I want to write a query that will show me only the users who are both tall and have blonde hair for instance. Eventually I'll need to match more than 2 attributes, but ill take what i can get for now.

    I know how to query people who are tall or people who have blonde hair. I've been using:

    ... WHERE Attribute IN ("tall","blonde")

    Any ideas?

    Thanks for your time,
    Ward

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select UID
      from daTable
     where Attribute in ('tall','blonde') 
    group
        by UID
    having count(*) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2005
    Posts
    2
    YOU'RE A GENIUS! THANKS SO MUCH!!

    It's so simple and it appears to be working in for every combination. I've tried playing around and adding a third attribute and changing the having clause to =3. That seems to work too. Should it work in all cases as long as I set having equal to the total number of parameters?

    Thanks,
    Ward

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aviddv1
    Should it work in all cases as long as I set having equal to the total number of parameters?
    yup, that's the idea

    the only thing that can trip you up is if for some reason there are multiple rows for the same attribute, but i would say that was faulty design -- but if it happens, you can get around it with

    .. HAVING COUNT(DISTINCT Attribute) = n
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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