Returning a Zero in a recordset that uses COUNT

[iamguyster]

Hi,

I am running a SP counting the records linked to a table containing colleges. So, if College A has 2 records in this table, College B 6 records and College C 4 records I see the following:

CollegeName NoOfRecords
-------------------------
College A 2
College B 6
College C 4

But if I have another College with no records, obviously it doesnt show up in the returned recordset. Is there a way to get it to show up. So I would get a recordset as follows

CollegeName NoOfRecords
-------------------------
College A 2
College B 6
College C 4
College D 0

Thanks for any help. If anyone thinks they can help and would like to see the SQL I used I would be happy to provide it. It consists of outerjoins across 5 tables so I'd rather know if there is an easy explanation before muddying the waters with a load of SQL in my post.

G

 

[donutman]

What is probably happening is that an inner join is not allowing rows to be included that would otherwise return a null value for the count.
You should also be aware that you can change Nulls to zero with the ISNULL() function.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[hmckillop]

It must be to do with a join somewhere (guess you definitely need on on the College table - containing collegeName field), probably need the SQL to be sure of any response.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[jhall01]

Assuming you have a table of colleges and a table of records of colleges you will want to do a left outer join between your table of colleges and the records table.

by doing a left outer join you will get nulls back where there are no records. You can then use the coalesce() to replace the null values with a numeric value:

coalesce("field","value to replace null")

The following will replace the Null with a 0
coalesce(yourField, 0) yourField