Hi

I am using ASE15 on solaris

I have 2 tables MC and AA

MC has mc.ID and mc.Name columns

AA has aa.ID aa.Name and aa.Txt columns


MC data

1,AAA
2,BBB
3,CCC

AA data

1,AAA,blah blah blah
2,BBB,blah blah blah
3,BBB,blah blah blah
4,AAA,blah blah blah
5,AAA,blah blah blah

I want my sql I run to output counts for all Names even if there is a no rows Likeb below

AAA 3
BBB 2
CCC 0

When I run in my sql it will show counts for AAA and BBB
AAA 3
BBB 2

But not CCC as I guess there is no rows returnded from the count , is there away I can get it to display the 0 count

My code below

select MC.Name , count(*)as MsgChan from MC as MCA Inner join AA as LT on MC.Name = LT.Name
group by SC.s_mcname

Any pointers appreciated.

Thanks

Does Sybase have GROUP BY ALL syntax? I know MS SQL Server has that, and it is intended for this sort of thing, but I do not know when in the evolution of Sybase/SQL Server it was introduced.

change your join from inner to left outer, and count only the matching keys in the right table