CODEWITHSUNDEEP
oracle-databaseoracle11g
HashTags
HashTags

Reputation: 105

Oracle Select Query on Same Table (self join)

It seems to simple, but not getting desired results

I have a table with there data

Team_id, Player_id, Player_name Game_cd
1            100     abc        24
1            1000    xyz        24
1            588     ert        24
1            500     you        24
2            600     ops        24
2            700     dps        24
2            900     lmv        24
2            200     hmv        24

I have to write a query to get a result like this

Home_team home_plr_id home_player away_team away_plr_id away_player
1            100       abc          2         600         ops
1            1000      xyz          2         900         lmv

The query I wrote

select f1.Team_id as home_team, 
        f1.player_id as home_plr_id,
        f1.player_Name as home_player,
        f2.Team_id as away_team, 
        f2.player_id as away_plr_id,
        f2.player_Name as home_player

from game f1, game f2
where 
f1.team_id<> f2.team_id and
f1.game_cd = f2.game_cd

Upvotes: 0

Views: 563

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Alternative to @Radagast81's self-join is pivot, available in your Oracle version:

select home_plr_id, home_plr_name,  away_plr_id, away_plr_name
  from (select game.*, 
               row_number() over (partition by team_id order by player_id) rn 
          from game)
  pivot (max(player_id) plr_id, max(player_name) plr_name 
         for team_id in (1 home, 2 away))

SQL Fiddle

Players have to be numbered somehow (here by ID), it can be done by name, null or even random. This numbering is needed only to put them in same rows. Pivot works also if numbers of players in teams differs.

Upvotes: 1

Radagast81
Radagast81

Reputation: 3016

It is not clear how you want to pair a home player with an away player. But provided that you don't care about that, the following might be what you are looking for:

WITH game_p AS (SELECT team_id, player_id, player_name, game_cd
                     , ROW_NUMBER() over (PARTITION BY team_id, game_cd ORDER BY player_id) pos
                     , dense_rank() over (PARTITION BY game_cd ORDER BY team_id) team_pos 
                  FROM game) 
SELECT NVL(f1.game_cd, f2.game_cd) AS game_cd
     , f1.Team_id as home_team
     , f1.player_id as home_plr_id
     , f1.player_Name as home_player
     , f2.Team_id as away_team
     , f2.player_id as away_plr_id
     , f2.player_Name as away_player
  FROM (SELECT * FROM game_p WHERE team_pos = 1) f1
  FULL JOIN (SELECT * FROM game_p WHERE team_pos = 2) f2
  ON f1.game_cd = f2.game_cd
  AND f1.pos = f2.pos

The new column POS gives any player of each team a position to pair them with the other team. The new column TEAM_POS is to get the team_id mapped to the values 1 and 2, as the team_id's can differ per game.

Finally do a FULL JOIN to get the final list. If the number of players are allways the same for both teams you can do a normal join instead...

Upvotes: 1

Related Questions