Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Multiple SQL queries

  1. #1
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348

    Multiple SQL queries

    Say I have the table structure as seen below:


    food_descriptions
    -------
    food_id
    description

    nutrient_data
    -------
    nutrient_id
    food_id
    nutrient_value


    Example: Say I want to get the total weight of carbohydrates present where food_id = 888 I would do something like:

    SELECT food_descriptions.description, nutrient_data.nutrient_value
    FROM food_descriptions, nutrient_data, weight
    WHERE food_descriptions.food_id = '888' AND food_descriptions.food_id = nutrient_data.food_id AND nutritional_data.nutrient_id='carb'

    That will give me the weight of carbohydrates for 100g of food 888 (all nutrient vaues are lister per 100g). If I wanted to get the protein and fibre weight as well, how would I do it, without creating three seperate SQL queries for each nutrient? I want to do it as a single query to cut back on the number of times I'm querying the database and creating an overhead. This isn't so bad when you're dealing with one food source, but if I want the values for over 100 food sources, then querying the database 3 times for each of them is out of the question.

  2. #2
    Registered GCT13's Avatar
    Join Date
    Aug 2003
    Location
    NYC
    Posts
    480
    Was "nutritional_data.nutrient_id='carb'" supposed to be for table: nutrient_data?

    What is 'carb' in your SQL sample?

    What is table 'weight'?
    ....

  3. #3
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348
    Originally posted by GCT13
    Was "nutritional_data.nutrient_id='carb'" supposed to be for table: nutrient_data?

    What is 'carb' in your SQL sample?

    What is table 'weight'?
    Yeah, sorry, I mean nutrient_data

    'carb' is a nutrient_id for carbohydrate... as 'protein' would be a nutrient_id for protein. It makes up a composite key field along with food_id. Example (nutrient_data table):

    food_id | nutrient_id | nutrient_value
    -----------------------------------
    888 | carb | 4.5
    888 | protein | 2.4
    888 | fibre | 8.3

    Forget the weight table. I'm actually trying to simplify a more complex problem to make it easier for people to understand. If I can get some help with this, then I should be able to find a solution to the more complex problem.

  4. #4
    Registered GCT13's Avatar
    Join Date
    Aug 2003
    Location
    NYC
    Posts
    480
    I think you need a new table "nutrient" so you don't have "carb", "protein", etc. listed hundreds or thousands of times in the nutrient_data table:

    nutrient table
    nutrient_id | nutrient_name
    -----------------------------------
    1 | carb
    2 | protein
    3 | fibre

    And then nutrient_data.nutrient_id would become a numeric column corresponding to nutrient.nutrient_id

    Am I making sense? Gotta run now. I'll think more about your problem when I get back.
    ....

  5. #5
    Junior Registered
    Join Date
    Jan 2004
    Posts
    6
    I suggest you put all that on one table, and not break it up. The way I do things, is if two tables depend on eachother, I put everything into one. It makes more sense, takes a load off the SQL server, and makes your code cleaner.

    But if you don't want to (or can't) do that.. too bad, do it anyways.

  6. #6
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348
    Actually, the nutrient id is already a numeric value and has its own table. But again, I'm trying to keep things as simple as possible to understand.

    The only way I can think to do it would be to do something like:

    SELECT food_descriptions.description, nutrient_data.nutrient_value
    FROM food_descriptions, nutrient_data
    WHERE food_descriptions.food_id = '888' AND food_descriptions.food_id = nutrient_data.food_id AND ( nutritional_data.nutrient_id='carb' OR nutritional_data.nutrient_id='protein' OR nutritional_data.nutrient_id='fibre');

    But then, that would return three rows for each food source which isn't very effecient either.

  7. #7
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348
    Originally posted by darkane
    I suggest you put all that on one table, and not break it up. The way I do things, is if two tables depend on eachother, I put everything into one. It makes more sense, takes a load off the SQL server, and makes your code cleaner.
    darkane, that's actually a really bad way to do things. You're burning your bridges, so to speak, and as your database grows it actually becomes less efficient for a whole host of reasons. Go read up on "database normalization" for more details.

  8. #8
    Registered GCT13's Avatar
    Join Date
    Aug 2003
    Location
    NYC
    Posts
    480
    My meeting's been delayed.
    Originally posted by chromate
    Actually, the nutrient id is already a numeric value and has its own table. But again, I'm trying to keep things as simple as possible to understand....
    chromate, you've killed my momentum.

    Could you post your entire unedited database structure? Thanks.
    ....

  9. #9
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348
    You really don't want the whole thing. Trust me! It's way to big to even begin posting it here. That's why I'm having to simplify it.

    Can it not be done with the tables I've given you?

  10. #10
    Registered GCT13's Avatar
    Join Date
    Aug 2003
    Location
    NYC
    Posts
    480
    I have a feeling to really get the answer you're after, you're going to have to post all the tables.
    ....

  11. #11
    Registered GCT13's Avatar
    Join Date
    Aug 2003
    Location
    NYC
    Posts
    480

    Re: Multiple SQL queries

    Originally posted by chromate
    If I wanted to get the protein and fibre weight as well, how would I do it, without creating three seperate SQL queries for each nutrient?
    Check this out and let me know if it works for ya:

    Code:
    SELECT nutrient_data.nutrient_id, nutrient_data.nutrient_value
    FROM food_descriptions, nutrient_data
    WHERE food_descriptions.food_id = 888
    Would return:


    nutrient_id | nutrient_value
    -----------------------------------
    carb | 8.3
    protein | 3.7
    fibre | 1.2
    ....

  12. #12
    Junior Registered
    Join Date
    Jan 2004
    Posts
    6
    chromate: Something tells me I've done more database programming than you, so don't criticize my methods so quickly.

    The efficiency of having all that information on a single table only becomes taxing if you have somewheres around 250,000 rows, which I'm assuming you don't. It simplifies small projects, that's all.

    And I'd appreciate it if you didn't preach about database normalization. That was a term created by book makers to cash in on a 'new theory' for an obvious db administration process.

    So the moral of this story, is that you're making things harder than they need to be, while saving about 2 clock cycles on your web server.

    But hey, what do I know.. I've only been programming for 15 years.

  13. #13
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348
    We're not only talking about database effeciency though are we? If you have had to deal with the number of poorly designed databases that I have, you would know what I mean!

    I'm not preaching anything to you. You've made 5 posts here. How do I know how experienced you are? Picture it from my point of view... you come on here and basically post "bung it all into one table and everything will be ok." What am I supposed to think? That you're an expert? Not likely! So assuming you're not that experienced, I suggest that you might like to read up on normalization. What's wrong with that?

    In all your 15 years of experience, I'm surprised you haven't heard of a person called Dr Codd. He, in fact, developed normalization techniques... Oh yeah, and something called the relational database! Where the actual "normalization" term comes from is of no importance at all, be it bookmakers or not. And if it's such an obvious DBA technique, I'm surprised you don't employ it.

    If you're suggesting that performance wont suffer until you have close to 250,000 rows, you are way off the mark! You can't possibly quantify it in that way. There are too many other factors to be taken into account, such as the number of requests being made per minute (or whatever) and what's actually being requested.

    If you actually read my posts you'll see that I'm trying to create a simplified model of a much larger problem. Putting it all into one table isn't even a remote option.


  14. #14
    Future AstonMartin driver r2d2's Avatar
    Join Date
    Dec 2003
    Location
    UK
    Posts
    1,608
    Wont each food have a value for each nutrient? Or do you assume the value is 0 if the there is no row in the nutrient_data table?

    I cant see why you dont put a field for the value of each nutrient in the food_descriptions table? Since every food will have a value for each of these, it makes sense doesnt it?

    Then you could just do:
    Code:
    SELECT description, carb, protein, fibre
    FROM food_descriptions
    WHERE food_id = 888
    GCT13, you last solution returns 3 rows which chromate wasnt too keen on either, and doesnt return the food description - not sure if you want that too chromate?

    Chromate, I realise this is similar to darkanes suggestion, but I dont think you have said why you have a separate table for the value of each nutrient of each food.
    Last edited by r2d2; 02-05-2004 at 05:49 AM.

  15. #15
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348
    It's because I'm dealing with a really large database. There are other fields in the nutrient_data table that are foreign keys in other tables. As an example, two of the fields in the nutrient_data table that I haven't mentioned are upper limits and lower limits of deviation found for each nutrient in each of the foods.

    If it were just the tables I originally listed, then I would have done as darkane suggested as they have a 1 to 1 relationship anyway. In fact, I think darkane and I are probably agreeing on a lot of stuff, but he doesn't realise that they aren't my actual tables I'm using. They're just simplified examples.

    But to give you an idea of scale... I'm dealing with 446,358 rows in the nutrient_data table alone! (way more than darkane's supposed threshold of 250,000 before things start slowing down). Each of those rows have 18 different fields (including key fields). So as you can see, it's a pretty big database and there's a lot of stuff that I haven't listed for that reason.

    What I need is exactly the result that your code would produce, r2d2. So it would return 1 row with each of those fields in your sql using just 1 sql statement but (and this is the big but) using the table structure I've given.

    But basically, if it can't be done with the table structure I have given, then it can't be done at all with the actual database I'm dealing with. I'm sure it can be done, in fact I've done something similar in the past, but I just can't remember how to do it.
    Last edited by chromate; 02-05-2004 at 07:44 AM.

Similar Threads

  1. Google bot using multiple sessions.
    By Dan Morgan in forum Search Engine Optimization
    Replies: 3
    Last Post: 03-29-2004, 04:32 PM
  2. lots of queries ? is this bad ?
    By s2kinteg916 in forum General Management Issues
    Replies: 3
    Last Post: 11-25-2003, 07:10 AM
  3. multiple websites ?
    By s2kinteg916 in forum General Management Issues
    Replies: 4
    Last Post: 11-23-2003, 09:46 PM
  4. multiple sites + amazon and cj.com ?
    By s2kinteg916 in forum Advertising & Affiliate Programs
    Replies: 5
    Last Post: 11-23-2003, 09:41 PM
  5. Multiple Domains?
    By Mike in forum Domain Names
    Replies: 11
    Last Post: 08-14-2003, 08:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •