October 23, 2008 at 8:10 am
I'm trying to select data from two tables, say Ingredients and Calories. Not all ingredients have a Calories table entry.
I'd like the data table to show all items, and simply leave Calories blank if there is no value, e.g.
Ingredients.Name Calories.Amount
_________ ______________
Salt 12
Water
Oil 60
Paper
Sugar 90
What I've got is pretty simple, but would not show Water or Paper in the above example:
Select I.Name, C.Amount
From Ingredients I, Calories C
Where I.Name = C.Name
What do I need to do to show each row regardless of whether there is an entry in the table Calories?
October 23, 2008 at 8:19 am
ellen.horn (10/23/2008)
Select I.Name, C.AmountFrom Ingredients I, Calories C
Where I.Name = C.Name
This is simply an OUTER JOIN:
SELECT i.Name, c.Amount
FROM Ingredients i
LEFT OUTER JOIN Calories c ON i.Name = c.Name
October 23, 2008 at 8:26 am
Hi
You can use th function Isnul the link contain examples and the sintaxe
http://www.w3schools.com/SQL/sql_isnull.asp
Regards
October 23, 2008 at 9:07 am
Hi d_gomes2
Using the ISNULL function only would not work in this instance as the nulls are creating because of missing records in the catories table. If it was null values then you could use the ISNULL function. So the answer supplied by Chris would work for this.
You could modify it so that it would return a default of 0 for the missing records and use the ISNULL function 🙂
SELECT i.Name, ISNULL(c.Amount,0)
FROM Ingredients i
LEFT OUTER JOIN Calories c ON i.Name = c.Name
Regards
Richard...
http://www.linkedin.com/in/gbd77rc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply