help please

[jag20]

i have 2 tables
games(winner_team_ID,looser_team_ID,game_ID,start_date_ID,finish_date_ID)
teams(team_ID,team_name,lig,division)

i need to a list of all the teams names with the games that they won or lost

 

[SantaMufasa]

Jag,

When you say, "i need to a list of all the teams names with the games that they won or lost," do you mean you need to see the individual games they won or lost, or do you want to see the number of games they won or lost?

Here is some very limited sample data for each table:

select * from teams;

   TEAM_ID TEAM_NAME LIG DIVISION
---------- --------- --- --------
         1 Yankees   A   E
         2 Red Sox   A   E
         3 Giants    N   W

3 rows selected.

select * from games;

WINNER_TEAM_ID LOOSER_TEAM_ID    GAME_ID START_DATE_ID  FINISH_DATE_ID
-------------- -------------- ---------- -------------- --------------
             2              1       2786 14-AUG-04      14-AUG-04
             1              3       2795 16-AUG-04      16-AUG-04
             1              3       2803 17-AUG-04      17-AUG-04
             1              2       2773 13-AUG-04      13-AUG-04

4 rows selected.

Here is the code to show individual game results:

break on a
col a heading "Team|Name" format a10
col b heading "Won/|Lost" format a4
col c heading "Game|ID" format 9999
col d heading "Game|Start|Date" format a9
select team_name a, 'W' b, game_id c, start_date_id d
  from games, teams
 where winner_team_id = team_id
union
select team_name a, 'L' b, game_id c, start_date_id d
  from games, teams
 where looser_team_id = team_id
order by a, d
/

                      Game
Team       Won/  Game Start
Name       Lost    ID Date
---------- ---- ----- ---------
Giants     L     2795 16-AUG-04
           L     2803 17-AUG-04
Red Sox    L     2773 13-AUG-04
           W     2786 14-AUG-04
Yankees    W     2773 13-AUG-04
           L     2786 14-AUG-04
           W     2795 16-AUG-04
           W     2803 17-AUG-04

8 rows selected.

Here is code to show summed game results (...and there are dozens of code alternatives for this one):

col e heading "Wins"
col f heading "Losses"
select a, sum(nvl(e,0)) e, sum(nvl(f,0)) f
from
(select	 team_name a, count(*) e, null f
from teams, games
where team_id = winner_team_id
group by team_name
union
select	 team_name a, null e, count(*) f
from teams, games
where team_id = looser_team_id
group by team_name)
group by a;

                      Game
Team       Won/  Game Start
Name       Lost    ID Date
---------- ---- ----- ---------
Giants     L     2795 16-AUG-04
           L     2803 17-AUG-04
Red Sox    L     2773 13-AUG-04
           W     2786 14-AUG-04
Yankees    W     2773 13-AUG-04
           L     2786 14-AUG-04
           W     2795 16-AUG-04
           W     2803 17-AUG-04

8 rows selected.

Let us know if this provides an acceptable solution for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:52 (13Oct04) UTC (aka "GMT" and "Zulu"), 22:52 (12Oct04) Mountain Time)

 

[jag20]

exactly!,
i wanted the summed game result.
thanks a lot!!!

but there is a different way to do it?
a simple one, because i want to share this with a partner but his has to get the things little by little.

and
thanks again!!!

 

[SantaMufasa]

Jag,

Sorry, I copied and pasted the wrong output in my previous post. Following is the proper code/output matchup:

col e heading "Wins"
col f heading "Losses"
select a, sum(nvl(e,0)) e, sum(nvl(f,0)) f
from
(select     team_name a, count(*) e, null f
from teams, games
where team_id = winner_team_id
group by team_name
union
select     team_name a, null e, count(*) f
from teams, games
where team_id = looser_team_id
group by team_name)
group by a;

Team
Name             Wins     Losses
---------- ---------- ----------
Giants              0          2
Red Sox             1          1
Yankees             3          1

So, did you want simpler code for the detail (row-by-row) output, or for the summary (team-totals) output?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:40 (13Oct04) UTC (aka "GMT" and "Zulu"), 09:40 (13Oct04) Mountain Time)