Posted by Jim Michaels on 02/24/06 00:56

"ZeldorBlat" <zeldorblat@gmail.com> wrote in message
news:1138992732.368447.225280@g49g2000cwa.googlegroups.com...
>
> 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.


yes. you can keep the user information in one table, and the answers in
another table, linked by an id.
with the answers, you can
SELECT answer FROM answers WHERE userid=5 AND answer IN ('Brown
Hair','Single','Tall')
although it might be good to have an extra auto_increment PRIMARY KEY id you
can use to identify individual rows to delete. Maybe userid and answer
should be made a KEY to prevent duplicates.





>

[Back to original message]