Page 1 of 1

Need help with a complex query

Posted: Sat May 08, 2010 6:56 am
by siko
Hi guys, I'm rather new to sql, this is my first time writing such a complex query, I'm really stucked.. Any help is sincerely appreciated.

Each user is allowed to choose any number out of 100 kinds fruits
Each user is allowed to choose any number out of 100 kinds of drinks

If I want to select a user who
1) is female
2) eats apple, orange, pear (they may eat other fruits but must at least eat these 3)
3) drinks coffee, tea, coke (they may like other drinks too but must at least eat these 3)

I have 3 tables

user_profile
-------------
-user_id
-name
-gender

favourite_fruit
---------------
-user_id
-fruit_id

favourite drink
---------------
-user_id
-drink_id

Re: Need help with a complex query

Posted: Sat May 08, 2010 11:50 am
by califdon
This should probably do it:

Code: Select all

SELECT name FROM user_profile, favourite_fruit, favourite_drink 
  WHERE gender = 'female'
  AND favourite_fruit.user_id = user_profile.user_id 
  AND favourite_drink.user_id = user_profile.user_id 
  AND fruit_id IN('apple','orange','pear')
  AND drink_id IN('coffee','tea','coke')

Re: Need help with a complex query

Posted: Fri May 14, 2010 7:35 am
by siko
Thanks alot cali,

I managed to get part of this query going, however there is one thing - it seems to not satisfy the condition that the users
- must eat at least ALL of the fruits/drink that i can specify, i.e. its basically an &(AND) condition. If i specify apple and orange, users who like only apples + other fruits but not orange, is not accepted, they have to like apple AND orange + any other fruits which are optional.

When I run a simpler query,

Code: Select all

SELECT name FROM user_profile, favourite_fruit, favourite_drink
  WHERE gender = 'female'
  AND favourite_fruit.user_id = user_profile.user_id
  AND fruit_id IN('apple','orange','pear')
I get results that resembles the following:

1. Jane
2. Jane
3. Mary
4. Susan
5. Susan
6. Susan

On investigating the results,

Jane eats apple and pear, but not orange (total 2)
Mary eats orange, but not apple and pear (total 1)
Susan eats all three. (total 3)

Is there a way to perform a query to SELECT a user that eats at least all of what I entered into the search?

Thanks again for your time!

Re: Need help with a complex query

Posted: Sun May 16, 2010 1:09 pm
by califdon
Yeah, sorry, I wasn't thinking very clearly, or I didn't read your requirements well enough. That's tougher than I thought it was. Let me think about that one for awhile.

Re: Need help with a complex query

Posted: Sun May 16, 2010 1:19 pm
by califdon
I don't have time to set up a DB to test this, but I'm inclined to think that you can do this. The principle here is that you're looking for names that have 6* specific records as defined in a subquery. I'm not that experienced with subqueries, but I think it can be done this way. Maybe someone else will correct me, or you can try it yourself:

Code: Select all

SELECT name FROM (
  SELECT name FROM user_profile, favourite_fruit, favourite_drink
    WHERE gender = 'female'
    AND favourite_fruit.user_id = user_profile.user_id
    AND favourite_drink.user_id = user_profile.user_id
    AND fruit_id IN('apple','orange','pear')
    AND drink_id IN('coffee','tea','coke')
) WHERE COUNT(name) = 6
* (or maybe just 3, if you're looking only for fruits or for drinks)