Join to "grouped by" subset

Dev Shed Forums Sponsor:

November 16th, 2012, 07:19 AM

boxersoft

Registered User

Join Date: Aug 2012

Posts: 15

Time spent in forums: 4 h 17 m 28 sec
Reputation Power: 0

Join to "grouped by" subset

Supposing I have a table of players and a table of scores. Players may play multiple games one or more times. For example:

Code:

drop table if exists players; 
create temporary table  players (id int, name varchar(10));
insert into players
    select 1, 'Tommy' union all
    select 2, 'Billy' union all
    select 3, 'Johnny' union all
    select 4, 'Freddie'; 
drop table if exists results;
create table results (playerID int, game varchar(10), score int);
insert into results
    select 1, 'Game1', 3 union all
    select 1, 'Game1', 4 union all
    select 1, 'Game2', 1 union all
    select 2, 'Game2', 3 union all
    select 2, 'Game2', 1 union all
    select 3, 'Game1', 2 union all
    select 3, 'Game2', 1 union all
    select 4, 'Game1', 1 union all
    select 4, 'Game1', 2;

Now, I want to select only players who played both Game1 and Game2, and I want their highest score in each game, i.e.:

PlayerID Name Game1 Game2
1 Tommy 4 1
3 Johnny 2 1

What's the best way to do that?

November 16th, 2012, 07:47 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 10 m 28 sec
Reputation Power: 390

Code:

select players.id,
       players.name,
       max(game1.score) as game1,
       max(game2.score) as game2
  from players
  join results as game1
    on players.id = game1.playerid
   and game1.game = 'game1'
  join results as game2
    on players.id = game2.playerid
   and game2.game = 'game2'
 group
    by id,
       name

November 16th, 2012, 07:48 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 10 m 28 sec
Reputation Power: 390

Code:

select players.id,
       players.name,
       max(game1.score) as game1,
       max(game2.score) as game2
  from players
  join results as game1
    on players.id = game1.playerid
   and game1.game = 'game1'
  join results as game2
    on players.id = game2.playerid
   and game2.game = 'game2'
 group
    by players.id,
       players.name

November 16th, 2012, 09:23 AM

boxersoft

Registered User

Join Date: Aug 2012

Posts: 15

Time spent in forums: 4 h 17 m 28 sec
Reputation Power: 0

Ahhhh... I was quite close, but kept trying to do the max() and/or group by in the join some how (and failing, naturally!).

Thanks very much.

November 16th, 2012, 11:44 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

Don't forget that a PRIMARY KEY is a prerequisite of all tables in SQL.

November 16th, 2012, 11:51 AM

boxersoft

Registered User

Join Date: Aug 2012

Posts: 15

Time spent in forums: 4 h 17 m 28 sec
Reputation Power: 0

Quote:

Originally Posted by cafelatte

Don't forget that a PRIMARY KEY is a prerequisite of all tables in SQL.

That was just an example to describe the problem as clearly as I could. The actual tables are quite different (nothing to do with games anyway) and they do have PKs.

That said, although I understand that PKs are strongly recommended, are they really needed in all circumstances?

November 16th, 2012, 12:08 PM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

You need some way of uniquely identifying each row. This is the PRIMARY KEY. This can be a natural key or a surrogate key. When using a surrogate, it's good practice to have a UNIQUE elsewhere within the table.

Strictly speaking, it's not always necessary. Your example illustrates that fact. But what if you wanted to find out whether Player 1 achieved her highest score on her first attempt or her second? You have no way of doing that because at present there is no way of uniquely identifying each row.

November 16th, 2012, 12:14 PM

boxersoft

Registered User

Join Date: Aug 2012

Posts: 15

Time spent in forums: 4 h 17 m 28 sec
Reputation Power: 0

Understood - thanks.

November 17th, 2012, 08:00 AM

boxersoft

Registered User

Join Date: Aug 2012

Posts: 15

Time spent in forums: 4 h 17 m 28 sec
Reputation Power: 0

OK, I now want to look at all results (rather than max. scores) but only from those individuals that played both games. So the tables again (with a PK now added to the results table) are:

Code:

create temporary table  players (id int, name varchar(10));
insert into players
    select 1, 'Tommy' union all
    select 2, 'Billy' union all
    select 3, 'Johnny' union all
    select 4, 'Freddie'; 
create table results (id int, playerID int, game varchar(10), score int);
insert into results
    select 1, 1, 'Game1', 3 union all
    select 2, 1, 'Game1', 4 union all
    select 3, 1, 'Game2', 1 union all
    select 4, 2, 'Game2', 3 union all
    select 5, 2, 'Game2', 1 union all
    select 6, 3, 'Game1', 2 union all
    select 7, 3, 'Game2', 1 union all
    select 8, 4, 'Game1', 1 union all
    select 9, 4, 'Game1', 2;

... and the output should now be something like:

ID PlayerID Name Game Score
1 1 Tommy Game1 3
2 1 Tommy Game1 4
3 1 Tommy Game2 1
6 3 Johnny Game1 2
7 3 Johnny Game2 1

Sorry to have to ask, I'm sure I should be able to work it out myself but I often seem to have something of a mental block when it comes to SQL, I'm afrraid...

Join to "grouped by" subset

Developer Shed Advertisers and Affiliates