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.
Question
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:
I want an sql that will display this table:
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 by thevinkLink to comment
Share on other sites
3 answers to this question
Recommended Posts