[SQL] Problem with Select Count

[thevink]

Hey,

I'm working with complicated tables, so I'm going to create a basic table to demostrate instead of the real values.

Suppose i have 2 tables:

Table 1:

ID	   Name
1		 John
2		 Steve
3		 Larry
4		 Mitch

Table 2:
NID	  Sport
2		  Hockey
2		  Football
1		  Hockey
4		  Football
4		  Hockey
4		  Baseball

I want an sql that will display this table:

Name	 # of Sports
John			  1
Steve			2
Larry			 0
Mitch			 3

I have a way of doing this, however I always miss Larry(people who have 0) because they get canceled out in the where clause(they are not in the joined table).

Any ideas how to do this?

EDIT: Actually this is a simple case in which I can code for, so I gave a bad example, but the theory is still there.

SELECT Person.Name, Count(Sport.Sport) AS CountOfSport

FROM Person LEFT JOIN Sport ON Person.ID = Sport.NID

GROUP BY Person.Name;

That works, however I can't apply that same skeleton on my real data, it just doesnt work.

Edited March 10, 2009 by thevink

[+Nik Louch Subscriber²]

SELECT

TABLE1.Name, COUNT(TABLE2.Sports)

FROM

TABLE1

LEFT JOIN

TABLE2 ON TABLE1.ID = TABLE2.NID

GROUP BY

TABLE1.Name

[Wilhelmus]

Have you tried left join the tables?

[thevink]

Yeah, I quickly discovered that in access.

I think I have isolated the problem.

This is a skeleton of the SQL i have:

select distinct  m.certificatenum, mb.benecdnm
from
vw_nsp_simple_member m
left join
	vw_nsp_join_member_memberbenefit mb on m.memberid = mb.memberid 
--*****change the clientnum and plannum below as req'd
	and (mb.clientnum = '0084551') --and (mb.plannum in ('a','b')) 
where 
	m.statuscd <> 3 and mb.statuscd = 1 and mb.benecdnm In ('ADD','LTD')
order by m.certificatenum

Now client #1000212 does not have ADD or LTD but it still should show up in the result, however with this SQL it does not show up.

I need the DISTINCT in the select clause because I have repetitive data in the database that I dont want to count. (Company database and only access I have is to read data by SELECT statements in View tables)

EDIT: I guess a simpler thing to do would be still use my basic tables at the top. Lets say I want to see the count of all people who play hockey or baseball or both, as well as showing the people who don't. So:

Name	 # of Hockey and/or Baseball
John			  1
Steve			1
Larry			 0
Mitch			 2

Edited March 10, 2009 by thevink