Joining Tables

[dotnet]

hey folks, I\'m having trouble with SQL,

 

 

I have two tables,

one is called GROUPS

and the other is called MEMBERS

 

GROUPS has a groupid field, and the rest are details about the group.

 

MEMBERS has a groupid and a memberid field...

the groupid in the MEMBERS table shows which group the member is with

 

 

I want to count how many members there are in a group

 

SELECT g.groupid, count(m.groupid) member# 

FROM groups g, members m

WHERE g.groupid = m.groupid

GROUP BY m.groupid

 

this works fine, the only problem is that it doesn\'t show groups with zero members...

 

I want all the groups whether they have members or not to show up...

 

thanks for your help

[pallevillesen]

SELECT DISTINCT g.groupid, count(m.groupid)

FROM groups g LEFT OUTER JOIN members m

ON g.groupid = m.groupid

GROUP BY m.groupid

 

OUTER JOIN should produce a new BIG table with NULL values where nothing fits in.... Using a normal join as you did omits these rows...

 

P.

[dotnet]

thank you very much...