SQL using AND
am 03.02.2006 19:19:54 von carrajo
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
Re: SQL using AND
am 03.02.2006 19:22:00 von Shion
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'
http://dev.mysql.com/doc/refman/5.0/en/join.html
Re: SQL using AND
am 03.02.2006 19:43:45 von carrajo
it didn work
Re: SQL using AND
am 03.02.2006 19:52:12 von zeldorblat
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.
Re: SQL using AND
am 03.02.2006 20:38:07 von carrajo
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.
Re: SQL using AND
am 06.02.2006 22:25:15 von Jonathan
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
Re: SQL using AND
am 23.02.2006 23:56:47 von Jim Michaels
"ZeldorBlat" wrote in message
news:1138992732.368447.225280@g49g2000cwa.googlegroups.com.. .
>
> 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.
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.
>