Select All Rows Where All Criteria Is Met Across Multiple Records

aviddv1 · #1 (**permalink**) 09-30-05, 17:06

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

r937 · #2 (**permalink**) 09-30-05, 18:40

Code:

select UID
  from daTable
 where Attribute in ('tall','blonde') 
group
    by UID
having count(*) = 2

aviddv1 · #3 (**permalink**) 09-30-05, 21:28

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

r937 · #4 (**permalink**) 09-30-05, 21:36

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

Internet Services

Web Development

Digital Marketing

Consumer Tech