How to select this..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
siko
Forum Commoner
Posts: 37
Joined: Tue Feb 16, 2010 11:28 pm

How to select this..

Post by siko »

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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: How to select this..

Post by mikosiko »

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)

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;
Miko
siko
Forum Commoner
Posts: 37
Joined: Tue Feb 16, 2010 11:28 pm

Re: How to select this..

Post by siko »

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:

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

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;
   )
adding in the IN syntax. Sounds about right to me.

Anyone please advise!

Appreciate your time greatly, thank you in advance again.
Post Reply