dotnet Posted January 21, 2003 Share Posted January 21, 2003 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 Quote Link to comment https://forums.phpfreaks.com/topic/72-joining-tables/ Share on other sites More sharing options...
pallevillesen Posted January 21, 2003 Share Posted January 21, 2003 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. Quote Link to comment https://forums.phpfreaks.com/topic/72-joining-tables/#findComment-206 Share on other sites More sharing options...
dotnet Posted January 22, 2003 Author Share Posted January 22, 2003 thank you very much... Quote Link to comment https://forums.phpfreaks.com/topic/72-joining-tables/#findComment-213 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.