Posted by NC on 02/15/06 07:52

carrajo wrote:
>
> I have 2 tables ( below ), keep in mind that a user can have hundreds
> of answers.
>
> user
> -------
> uid
> fullname
>
> 1 John Smith
>
> user_answers ( users can have unlimited amount of answers )
> -----
> uid
> answer
>
> 1 Brown Hair
> 1 Single
> 1 Tall
> 1 55
> 1 White
>
> With help, I can now search all users that matched what I selected.
>
> I.E - Selected criteria ( 'Brown Hair', 'Single' and 'Tall' )
>
> 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')
>
> What I need to do is to modify the search above so I can do the
> following:
>
> I.E - Selected criteria ( 'Brown Hair', 'Single' and 'Tall' )
>
> - Return all users that answered 'Brown Hair', 'Single' and 'Tall'
> - Return all users that answered 'Brown Hair'
> - Return all users that answered 'Single'
> - Return all users that answered 'Tall'
> - Return all users that answered 'Single', 'Tall'
> - Return all users that answered 'Single', 'Brown Hair'
> - Return all users that answered 'Tall', 'Brown Hair'
>
> BUT NOT
>
> - users that answered 'Brown Hair', 'Single', 'Tall' and 55
> - users that answered 'Brown Hair', 'Single', 'Tall' and White
>
> Basically, return an exact match or users that answered only 1 or more
> of the criteria I selected.

You need to JOIN multiple copies of user_answers table with the user
table:

SELECT DISTINCT
user.uid AS uid,
user.fullname AS fulllname
FROM
(user LEFT JOIN user_answers AS a1 ON user.uid=a1.uid)
LEFT JOIN user_answers AS a2 ON user.uid=a2.uid
WHERE (a1.answer = 'Brown Hair'
OR a1.answer = 'Single'
OR a1.answer = 'Tall')
AND a2.answer <> 'White'
AND a2.answer <> '55';

Cheers,
NC

[Back to original message]