SELECT food.description, C nutrient_data.nutrient_value, P nutrient_data.nutrient_value, F nutrient_data.nutrient_value
FROM food, nutrient_data as C, nutrient_data as P, nutrient_data as F
WHERE (
food.food_id = C.food_id AND C.nutrient_id = '1'
) AND (
food.food_id = F.food_id AND F.nutrient_id = '2'
) AND (
food.food_id = P.food_id AND P.nutrient_id = '3'
) AND food.food_id = '12';


Ok... I know this is probably REALLY badly formed SQL Sorry, I'm not that good at it.

It works if values for nutrient_id 1, 2 and 3 are present, otherwise it wont return anything. I know this is what you would expect, but I want it to return something if one of the nutrient's aren't present. I would use an OR but then I get hundreds of different results.

What I need is for it to return 0 for a value for a nutrient if it isn't present rather than simply not returning any rows at all. That way, I can still use the other values that are there. Can this be done? Or am I just being confusing and ridiculous?

Select a.columnA, NVL(b.columnB, 0)
from tableA a
LEFT OUTER JOIN
tableB b ON
a.id = b.id;

Problem with that is the value wont be "null". It simply wont exist, ie there wont be a value. I need it to be set to 0 if the value isn't present whilst still returning the other values or simply use the value if it is there.

Hard to explain

That is the definition of NULL - when a field does not have a value in it.

That's what I thought, but I'm using MySQL, and...

http://www.mysql.com/doc/en/Problems_with_NULL.html

r937, Thanks SO much! That works a treat! You had the P's and F's mixed up, but I'll forgive you for that Thanks again.