Posted by ZeldorBlat on 02/03/06 20:52

carrajo wrote:
> basically the tables and data that I'm working with:
>
> user
> -------
> uid
> fullname
>
> INSERT INTO user SET uid = '1', fullname = 'John Smith'
>
>
> user_answers ( users can have unlimited amount of answers )
> -----
> uid
> answer
>
> 1 Brown Hair
> 1 Single
> 1 Tall
> 1 55
>
> INSERT INTO user_answers SET uid = '1', answer = 'Single'
> INSERT INTO user_answers SET uid = '1', answer = 'Tall'
> INSERT INTO user_answers SET uid = '1', answer = '55'
> INSERT INTO user_answers SET uid = '1', answer = 'Brown Hair'
>
>
> I want to return all users that have Brown Hair, Single and Tall
>
> This doesn't work for me:
>
> SELECT users.* FROM users, user_answers
> WHERE users.uid = user_answers.uid
> AND user_answers.answer = 'Brown Hair'
> AND user_answers.answer = 'Single'
> AND user_answers.answer = 'Tall'
>
> Could some help please.
>
> Thanks

Let's consider what happens when you have one user and they have three
answers. After doing the projection (with the query you have above)
we'd get something like this:

uid fullname answer
1 Joe Brown Hair
1 Joe Single
1 Joe Tall

Ok, now apply the where clause. Of those three rows, how many have
answer = 'Brown Hair' AND answer = "Single" AND answer = "Tall" ?
None. Hence you get no rows.

Unfortunately, there isn't a really straightforward way to do this.
One option:

select users.*
from users
where uid in (select uid from user_answer where answer = 'Brown Hair')
and uid in (select uid from user_answer where answer = 'Single')
and uid in (select uid from user_answer where answer = 'Tall')

There are plenty of other ways to do it, too.

[Back to original message]