table join

debbiecoates · July 2nd, 2008, 10:14 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

joefawcett · July 2nd, 2008, 10:38 AM

Without looking too carefully it sounds like you need a LEFT OUTER JOIN:

Code:

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)

debbiecoates · July 2nd, 2008, 01:39 PM

Brilliant, Thank you very much

Debbie x