This is actually from a more complex problem I posted some time ago, but I have narrowed down the problem.
The actual problem is here: viewtopic.php?f=2&t=116219
I have a table with 2 columns, namely user_id and fruits.
One user can choose any number out of 100 fruits. This is stored in those 2 columns like so:
user_id | fruit
-------------------
65 | apple
65 | pear
66 | mango
66 | apple
66 | pear
67 | apple
67 | banana
How do I select a user that eats mango AND apple AND pear.
What I cannot figure out is how to check for these 3 and make sure the user_id is the same one.
How to select this..
Moderator: General Moderators
Re: How to select this..
Sure it has more complex and generic solutions... but this one could work ... the list of fruits should be a parameter and also replace the hardcode number 3 with a variable representing the number of parameters (fruits)
Miko
Code: Select all
SELECT a.userid, count(a.fruit) AS nMatch
FROM userfruit a
WHERE a.fruit IN ('mango', 'apple','pear')
GROUP by a.userid
HAVING nMatch >= 3;Re: How to select this..
Ok, got ya, I see the logic.
But what if I want to apply this to the main problem - if you follow the link to viewtopic.php?f=2&t=116219.
How do I control with nMatch >= 3 since I will be matching other stuff as well..
Again I'm not asking for spoon feeding, but I'm truely having problems understanding this (this is my first time doing databases and writing queries)..
I just found out that you can do sub queries, I'm actually not really sure how to do it, but i'm going to attempt one, hopefully someone can spot my mistake and offer some advise:
Will this work to select a user that is
- female
- favourite fruit of AT LEAST mango AND apple AND pear
The part I'm not sure is, what exactly is the whole chunk of the inner SELECT statement going to return as a 'condition' in the outer query. Am I right to say it returns the result from the inner select statement, lets say if the result is 20, 24, 32 (these users like mango AND apple AND pear), and sets the condition that only these few user ids have a chance of being accepted in the final result? (since it needs to do the check for gender as well?)
Or should I do something like
adding in the IN syntax. Sounds about right to me.
Anyone please advise!
Appreciate your time greatly, thank you in advance again.
But what if I want to apply this to the main problem - if you follow the link to viewtopic.php?f=2&t=116219.
How do I control with nMatch >= 3 since I will be matching other stuff as well..
Again I'm not asking for spoon feeding, but I'm truely having problems understanding this (this is my first time doing databases and writing queries)..
I just found out that you can do sub queries, I'm actually not really sure how to do it, but i'm going to attempt one, hopefully someone can spot my mistake and offer some advise:
Code: Select all
SELECT * FROM user_profile, favourite_fruit
WHERE user_profile.gender = 'female'
AND favourite_fruit.user_id = user_profile.user_id
AND (
SELECT favourite_fruit.user_id, count(favourite_fruit.fruit_id) AS nMatch
FROM favourite_fruit
WHERE favourite_fruit.fruit_id IN ('mango', 'apple','pear')
GROUP by favourite_fruit.user_id
HAVING nMatch >= 3;
)
- female
- favourite fruit of AT LEAST mango AND apple AND pear
The part I'm not sure is, what exactly is the whole chunk of the inner SELECT statement going to return as a 'condition' in the outer query. Am I right to say it returns the result from the inner select statement, lets say if the result is 20, 24, 32 (these users like mango AND apple AND pear), and sets the condition that only these few user ids have a chance of being accepted in the final result? (since it needs to do the check for gender as well?)
Or should I do something like
Code: Select all
SELECT * FROM user_profile, favourite_fruit
WHERE user_profile.gender = 'female'
AND favourite_fruit.user_id = user_profile.user_id
AND favourite_fruit.user_id IN (
SELECT favourite_fruit.user_id, count(favourite_fruit.fruit_id) AS nMatch
FROM favourite_fruit
WHERE favourite_fruit.fruit_id IN ('mango', 'apple','pear')
GROUP by favourite_fruit.user_id
HAVING nMatch >= 3;
)
Anyone please advise!
Appreciate your time greatly, thank you in advance again.