asked on

SELECT query on two levels (detail and summary)

Hi experts,

I'm plain out of practice with SQL!

I need to get ID and names of all the players who were selected for their team's most recent match.

I have a fixtures_results table, showing all fixtures and results for each team over several seasons. Primary key is ID. This would have all match dates (fr_match_date) for each of 8 teams (team_code). fr_season_lit would be eg. 2016-17; fr_season_start would be 2016. So let's say I'm interested in fr_team_code = 'S' and fr_season_start = 2016.

I have a players_members table, listing all players at the club. Primary key is again, ID. So this would have first_name, surname etc.

To link these tables there is a fixtures_players table showing who was selected for each fixture. Primary key is fp_player_id and fp_fixture_id which relate to the respective IDs in the other tables.

So I need to find the date of the most recent fixture for the relevant team from fixtures_results, whether there were players selected for that game, if not the fixture before that etc. This could be resolved by an inner join between fixtures_players and fixtures_results.

I then need to find the players' names from players_members via the fp_player_id of the fixtures_players table.

Currently I'm working in MySQL, but would appreciate the MSSQL solution also, as I'm sure that would involve a CTE which MySQL doesn't support.

If you need any other info, please ask.

Cheers

Scott Pletcher

SELECT fp_player_id, pm.first_name, pm.surname /*, fr_match_date AS most_recent_match_date*/
FROM (
SELECT fr.*, fp.*, ROW_NUMBER() OVER(PARTITION BY fp.fp_player_id ORDER BY fr.fr_match_date DESC) AS row_num
FROM fixtures_results fr
INNER JOIN fixtures_players fp ON fp.fp_fixture_id = fr.id
) AS derived
INNER JOIN players_members pm ON pm.id = fp.fp_player_id
WHERE row_num = 1

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Colin Brazier

ASKER

Thanks very much guys. Will have to take your word for it, at least for a while, as I don't have an MSSQL db yet.

Colin Brazier

ASKER

OK, after struggling with SQL Server Migration Assistant I now have a MSSQL DB!

Scott, pm.id = fp.fp_player_id should be pm.id = derived.fp_player_id

All 3 give the same results, but I must have written the question wrongly!

The solutions all answer the question...What was each player's most recent match?

But I need to know, which players played in team S's most recent match?

Pawan Kumar

Have u checked last part of my comment? I think that is there what you are asking.

Scott Pletcher

@Colin:

Quite right on the join, I used the wrong alias.

I think you can just add the appropriate WHERE condition(s) to restrict it to a certain team:

SELECT fp_player_id, pm.first_name, pm.surname /*, fr_match_date AS most_recent_match_date*/
FROM (
SELECT fr.*, fp.*, ROW_NUMBER() OVER(PARTITION BY fp.fp_player_id ORDER BY fr.fr_match_date DESC) AS row_num
FROM fixtures_results fr
INNER JOIN fixtures_players fp ON fp.fp_fixture_id = fr.id
WHERE fr.fr_team_code = 'S' and fr.fr_season_start = '2016'
) AS derived
INNER JOIN players_members pm ON pm.id = derived.fp_player_id
WHERE row_num = 1

Colin Brazier

ASKER

No they both tell me when players last played for team S. So if Fred smith was dropped from team S three games ago, I don't want him listed - I only want the players who figured in team S's last game, eg last week.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Colin Brazier

ASKER

That's pretty much it for the MSSQL solution. You're not to know but all the fixtures are in place for the whole season, so all future matches have no players selected, so i had to put a join in the inner query.

DECLARE @season_start smallint
DECLARE @team_code char(1)

SET @season_start = 2016
SET @team_code = 'S'

SELECT fp_player_id, pm.first_name, pm.surname /*, fr_match_date AS most_recent_match_date*/
FROM (
    SELECT fr.*, fp.*
    FROM (
        /* get all results rows that match the given season, team code and last match date */
        SELECT *
        FROM fixtures_results
        WHERE fr_season_start = @season_start AND
            fr_team_code = @team_code AND
            fr_match_date = (
            /* get the last match date for the given season and team code */
            SELECT TOP (1) fr_match_date
            FROM fixtures_results fr1 INNER JOIN fixtures_players fp1 ON fr1.id = fp1.fp_fixture_id
            WHERE fr_season_start = @season_start AND
                fr_team_code = @team_code
            ORDER BY fr_match_date DESC )
    ) AS fr
    INNER JOIN fixtures_players fp ON fp.fp_fixture_id = fr.id
) AS derived
INNER JOIN players_members pm ON pm.id = derived.fp_player_id

Open in new window

I just need to know how to work it out in MySQL now! That's me done for the weekend, thanks both for your help.

Scott Pletcher

I used "standard" SQL code, so presumably the code would work in MySQL, except of course that the variables, @, are likely completely different in MySQL. But the subquerys and the joins should, at least in theory, be "standard".

Colin Brazier

ASKER

Ah yes, of course!

Colin Brazier

ASKER

I had to replace TOP (1) with LIMIT 1 but otherwise all good - thanks again.

Scott Pletcher

Ooh, right, sorry, forgot about the "TOP" part.