[RESOLVED] SQL 2000 SELECT/Count ?

[conwa02976]

I am using SQL Server 2000. I have a field in table one that stores values, in this case 35 thru 46. I am tyring to count each of those values in table two. I can write the count statement easy enough to give me what I expect to see. EValue are the values stored in table one and count is the count of each value in table two:

EValue CountofResponse
38 65
42 4
43 1
44 6
45 18
46 20

This is what I would like to see:

EValue CountofResponse
35 0
36 0
37 0
38 65
39 0
40 0
41 0
42 4
43 1
44 6
45 18
46 20

Here is the SQL I am using now:
SELECT dbo_tblMDD.EValue, Count(dbo_tblData.Response) AS CountOfResponse
FROM dbo_tblMDD INNER JOIN dbo_tblData ON (dbo_tblMDD.EValue = dbo_tblData.Response) AND (dbo_tblMDD.VID = dbo_tblData.VID) AND (dbo_tblMDD.PID = dbo_tblData.PID)
GROUP BY dbo_tblMDD.VID, dbo_tblMDD.PID, dbo_tblMDD.EValue
HAVING (((dbo_tblMDD.VID)=47) AND ((dbo_tblMDD.PID)="340109"))
ORDER BY dbo_tblMDD.EValue;


Thanks for you help in advance!!

[techgnome]

change your inner join to an outter join.... change your having to a WHERE and move it up to before the Group By.... change the COUNT to the following:

SUM(CASE
WHEN dbo_tblData.Response IS NOT NULL THEN 1
ELSE 0
END )

-tg

[conwa02976]

Worked like a charm!!

Thanks techgnome