PHP SQL - SQL using AND

Author

SQL using AND

carrajo

2006-02-08, 9:16 am

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

J.O. Aho

2006-02-08, 9:16 am

carrajo

2006-02-08, 9:16 am

it didn work

ZeldorBlat

2006-02-08, 9:16 am

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.

carrajo

2006-02-08, 9:16 am

Ahhhh, now it makes sense.

Thank you for your explanation. I'm glad you didn't just post a link to
something I didn't
understand in the first place.

Jonathan

2006-02-08, 9:16 am

carrajo wrote:
> it didn work
>
We can't help you...

.... this way, but we might be anle if you:

1. Provide us with the query/queries you tried
2. Be more specific about error messages (you will probably have some)

and other thing is that you could reconsider your table design, why not
store every individual as a row, specifying his features:

uid = 1, marital_status = single, age = 55, haircolor = brown
uid = 2, marital status = devorced, age = 27, haircolor = pink

This way you can just do a plain select to get the users you need,
instead of the more complex join query.

Jonathan

Jim Michaels

2006-02-23, 6:59 pm

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

>

Sponsored Links