Posted by carrajo on 02/15/06 06:29

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

• Next in forum: Re: MySQL searching only Criteria
• Prev in forum: Re: Google Maps API
• Thread view: MySQL searching only Criteria

[Reply to this message]