October 6, 2002 at 8:46 am
In the following statement it returns Null if it can't find just one of the rows in the Stats table. Can anyone help me with how I could get it to return a zero for a row that's not found so the total could be returned for the rows it does find.
SELECT [ID], Category, Card_Number,
(SELECT ISNULL(Stat1, 0) Stat1 FROM Stats WHERE Stats.ID = Category.ID1 AND Stats.[Month] = 9) +
(SELECT ISNULL(Stat1, 0) Stat1 FROM Stats WHERE Stats.PlayerID = Category.Player2 AND Stats.[Month] = 9) +
(SELECT ISNULL(Stat1, 0) Stat1 FROM Stats WHERE Stats.PlayerID = Category.Player3 AND Stats.[Month] = 9) AS CatScore
FROM Category WHERE (Description = 'myDescription') AND ([Year] = 2002)
October 6, 2002 at 11:08 am
That would be correct as if any one of the subqueires where clause is not met no value is returned therefore it gives you null. Do the ISNULL on the subquery to set to 0 if no value returns. May also want to consider looking at a query based on joins and aliasing stats table for each reference instead.
Something like this should do the trick and will result in fewer reads, so execution should be faster.
SELECT
Category.[ID],
Category.Category,
Category.Card_Number,
ISNULL(P1.Stat1,0) + ISNULL(P2.Stat1,0) + ISNULL(P3.Stat1,0) AS CatScore
FROM
Category
LEFT JOIN
Stats P1
ON
P1.[ID] = Category.ID1 AND
P1.[Month] = 9
LEFT JOIN
Stats P2
ON
P2.PlayerID = Category.Player2 AND
P2.[Month] = 9
LEFT JOIN
Stats P3
ON
P3.PlayerID = Category.Player3 AND
P3.[Month] = 9
WHERE
(Category.Description = 'myDescription') AND
(Category.[Year] = 2002)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply