Need help with a complex query

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

Need help with a complex query

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Need help with a complex query

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

Re: Need help with a complex query

Post 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!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Need help with a complex query

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Need help with a complex query

Post 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)
Post Reply