Multiple SQL queries

[chromate]

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.

[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'?

[chromate]

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.

[GCT13]

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.

[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.

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

[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.

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.

[chromate]

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.

[GCT13]

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.

[chromate]

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?

[GCT13]

I have a feeling to really get the answer you're after, you're going to have to post all the tables.

[GCT13]

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:


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


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

[darkane]

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.

[chromate]

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.

[r2d2]

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:


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.

[chromate]

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. :)

[chromate]

I have it working at last :)

SELECT food_descriptions.description, C.nutrient_value AS C, P.nutrient_value AS P, F.nutrient_value AS F
FROM nutrient_data C, nutrient_data P, nutrient_data F, food_descriptions
WHERE (
C.food_id = '888' AND C.nutrient_id= 'carb'
) AND (
P.food_id = '888' AND P.nutrient_id= 'protein'
) AND (
F.food_id = '888' AND F.nutrient_id= 'fibre'
) AND food_descriptions.food_id = '888'

Kind of complex, but it does the job in 0.0007 seconds :)


Produces:

Desc | C | P | F
-----------------
Salted Butter | 0.060 | 0.850 | 0.000

[GCT13]

Nice one.

[Chris]

Its fun writing complex sql statements. If you show them to someone who only knows how to write short standard ones they get impressed.

[Mike]

When you write food_descriptions.description for example, what does it mean? I've never seen a query which has a dot in it?

[chromate]

It's just so you can identify the table that a field should come from. If you had two or more tables with the same field name involved in the query, then it can be useful. I do it out of habbit really. It's not always needed.

SELECT name FROM employees WHERE id = '2';

Would be the same as...

SELECT employees.name FROM employees WHERE employees.id = '2';

But if you have more than one table that has the field "id" or "name" and you want to join those tables within your SQL query, then you would need to specify the required table followed by a dot and then the field name.

[Mike]

Sounds useful, thanks chromate:)

[r2d2]

Ah excellent, well done chromate. Will have to remember that technique as I had a similar kinda task for my site - a few less rows in the tables though, more like ~20 :) So I just stuck with multiple queries :)