MySQL searching only Criteria

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

Re: MySQL searching only Criteria

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'))

Re: MySQL searching only Criteria

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

Re: MySQL searching only Criteria

Works half way, it won't return the user that has exactly 'Brown Hair',
'Single', 'Tall'
but it will return the following:

- 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

- Return all users that answered 'Brown Hair', 'Single' and 'Tall'

Re: MySQL searching only Criteria

This works. Thanks for all your help guys.

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'))

OR (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')
)