outer join question, or how to display 0 counts in a group by

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Thu, 18 Apr 2002 22:00:07 GMT
Message-ID: <3cbf40c4.496728087@news.globix.com>

I have the following query and the following output

what id'd like it to show is, for each member_id and game_id if there are no rows in member_team still display game_id and count as 0. Right now it only displays 0 counts for member_ids that have no rows at all in member_team. I also have a games table that lists all the games, but I can't do two outer joins in the same query..

Any hints appreciated.

Thanx.

SQL> select m.id as member_id, game_id, count(game_id) as team_count from members m, member_team mt

        where
                  m.id=mt.member_id(+)
                and m.id<3
        group by m.id, game_id
        order by count(game_id) desc

  2 3 4 5 6 7
SQL> /  MEMBER_ID GAME_ID TEAM_COUNT
---------- ---------- ----------

         2         26         18
         2         17          7
         1         17          4
         2         22          3
         2         18          1
         2         45          1
         2         39          1
         2         36          1
         2         38          1
 -14222244                     0

.......
We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 750-3630
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email

Received on Thu Apr 18 2002 - 17:00:07 CDT