-
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
-
-
Its fun writing complex sql statements. If you show them to someone who only knows how to write short standard ones they get impressed.
-
When you write food_descriptions.description for example, what does it mean? I've never seen a query which has a dot in it?
-
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.
-
Sounds useful, thanks chromate:)
-
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 :)
