SELECT ALL ROWS WHERE ALL CRITERIA IS MET EVEN ACROSS MULTIPLE RECORDS

[aviddv1]

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

 

[elsenorjose]

SELECT UID, ATTRIBUTE
FROM TABLE
WHERE (ATTRIBUTE = 'TALL' AND ATTRIBUTE = 'BLONDE')

Just add more attributes as you need them, i.e.;

WHERE (ATTRIBUTE = 'TALL' AND ATTRIBUTE = 'BLONDE' AND ATTRIBUTE = 'BLUE EYES')

 

[aviddv1]

I tried that but it doesn't seem to work on my end.

thanks,
Ward

 

[johnwm]

You need a subquery (AKA nested query). Try:

http://dev.mysql.com/doc/mysql/en/subqueries.html

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[TonyGroves]

If you're using MySQL 4.1 or later, you could try:
[tt]
SELECT uid
FROM
(
SELECT
uid,
CONCAT(',',GROUP_CONCAT(attribute),',') a
FROM tbl1
GROUP BY uid
)
q
WHERE
a LIKE '%,tall,%' AND attrs LIKE '%,blonde,%'
[/tt]

 

[TonyGroves]

Or, slightly simpler:
[tt]
SELECT uid
FROM
(
SELECT uid, GROUP_CONCAT(attribute) a
FROM tbl1
GROUP BY uid
)
q
WHERE
FIND_IN_SET('tall',a)
AND FIND_IN_SET('blonde',a)
[/tt]

 

[r937]

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

easily extensible to 3 or more attributes

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

r937.com | rudy.ca

 

[TonyGroves]

Rudy's solution should work too, as long as you have no duplicate records. For example, if a particular UID has two 'tall' records but no 'blonde' record, that UID will be included in the result set.

 

[r937]

you're right, tony, but ...

1. that shouldn't happen, because {UID,Attribute} should be the primary key

2. even if it does happen, just change

... having count(*) = n

to

having count(distinct Attribute) = n


r937.com | rudy.ca