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
Need help with a complex query
Moderator: General Moderators
Re: Need help with a complex query
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
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,
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!
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')
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
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
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:
* (or maybe just 3, if you're looking only for fruits or for drinks)
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