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)