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

You can do an multiple outer joins in a single query if you do them within a inline view.

Daniel Morgan

NetComrade wrote:

> 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:01:50 CDT