Jump to content


Photo

Joining Tables


  • Please log in to reply
2 replies to this topic

#1 dotnet

dotnet
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 21 January 2003 - 01:43 AM

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

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 21 January 2003 - 10:20 AM


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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 dotnet

dotnet
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 22 January 2003 - 06:14 AM

thank you very much...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users