Posted by Bob Stearns on 02/15/06 07:42

carrajo wrote:
> Hey,
>
> 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.
>
> Thanks you very much
>
How about something like

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

[Back to original message]