|
Subject:
|
table join
|
|
Posted By:
|
debbiecoates
|
Post Date:
|
7/2/2008 10:14:27 AM
|
hello
I have created the following sql statement which works fine
SELECT I.[ItemisedGrantID], I.[ProjectID], CASE
WHEN I.SubCostType is NULL
THEN I.COSTTYPE ELSE I.COSTTYPE + ' - ' + I.SubCostType
END
AS Display,I.[total], g.Issued
, CASE WHEN G.Issued > I.Total then G.Issued - I.Total
WHEN G.Issued < I.Total then G.Issued - I.Total
--else 0
end
as 'xxx'
from V_ItemisedGrant I,
(SELECT ItemisedGrantID, sum(Amount) as Issued
from PROJECTITEMISEDOUTPUTCOSTS
GROUP BY ItemisedGrantID
)AS G
WHERE i.ItemisedGrantID = g.ItemisedGrantID
This will give me results like this
ItemisedGrantID/ProjectID/Display/Total/Issued/xxx
74/10000/Cost3/1456/1456/null
75/10000/Cost2/800/800/null
76/10001/Cost3/3244/3218/-6
77/10001/Cost2/1563/1562/-1
78/10001/cost1/2306/2314/8
the results are fine IF both tables are populated with the same ItemisedGrantID , However, if say no costs are registered in the
PROJECTITEMISEDOUTPUTCOSTS table for '10000' i want it to say 0
ie i want the query to look at all ItemisedGrant Items and only
PROJECTITEMISEDOUTPUTCOSTS if there are any, I know its a join, but I am not sure how to do it.
Would appreciate any help
Thank you
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
7/2/2008 10:38:47 AM
|
Without looking too carefully it sounds like you need a LEFT OUTER JOIN:
from V_ItemisedGrant I LEFT OUTER JOIN
(SELECT ItemisedGrantID, sum(Amount) as Issued
from PROJECTITEMISEDOUTPUTCOSTS
GROUP BY ItemisedGrantID
)AS G
ON i.ItemisedGrantID = g.ItemisedGrantID
--
Joe (Microsoft MVP - XML)
|
|
Reply By:
|
debbiecoates
|
Reply Date:
|
7/2/2008 1:39:17 PM
|
Brilliant, Thank you very much
Debbie x
|
|
