I have a database that manages items for a sports club. There are the following tables present that I wish to query:
- has an autonumber ID and contains name details and other personal info
- if this member is a player then a row exists on the player table. The ID is an autonumber and there is a member_id column which contains the ID of the member associated with this player.
- this table indicates when a player has been selected for a fixture. It has an id of the combination of fixture_id and player_id. It has no other relevant info.
- This table contains details of the fixture to be played (date, time, KO, opposition, location, referee etc.). It is keyed by an autonumber ID
The question I want to answer is "When was the last game for all players" and to show if they haven't yet been selected for a game.
So, I thought MEMBER JOIN PLAYER on player.member_id=member.id
and PLAYER LEFT JOIN onto SELECTION player.id=selection.player
_id and then SELECTION JOIN to FIXTURE on selection.fixture_id=fixtu
The reasons being:
1. I ONLY want Members who ARE Players - so standard join
2. I want ALL Players - regardless of if they have been selected - so LEFT JOIN with player at the Left.
3. If there is a Selection - I want the Fixture - but I only want one fixture - which was the most recent one played.
4. There are 2 further columns on Fixture - STATUS which is an ENUM and can have values not played, played, cancelled WE ONLY WANT played and DATE which is a standard My SQL DATE field.
There are some other predicates needed - but the above is the nuts and bolts. For completeness, I only want ADULTS - so I check the Member Birth Date and I only want certain Teams (so I join FIXTURE with HOME_TEAM) and I want to know the title of the position by joining the FIXTURE with the POSITION table.
Below is the FULL query that I have come up with but it times out - possibly unsurprisingly - so my question is if there is a better way - even if it means duplicating things on tables.
SELECT m.membership_id , m.id , m.lastname , m.firstname , m.gender ,
m.date_of_birth , m.active , p.active , p.type , p.section_id , f.date , home_team.name , position.description
FROM member m, player p
LEFT JOIN selection s ON s.player_id=player.id
JOIN fixture f ON f.id=s.fixture_id
JOIN home_team ON f.home_team_id=home_team.id
AND home_team.id IN(1,2,4,5,6,7,281)
JOIN position ON s.position_id=position.id
AND (m.date_of_birth<"1993-09-01" OR date_of_birth="0000-00-00")
AND f.id=(SELECT substring_index(group_concat(fs.id ORDER BY fs.date DESC), ',', 1 ) FROM fixture fs, selection ss WHERE fs.status="played"
AND fs.home_team_id IN(1,2,4,5,7,281)
AND fs.id=ss.fixture_id AND ss.player_id=s.player_id )
GROUP BY p.id
ORDER by p.active DESC,
The idea behind the last predicate is to get the fixture whose date is the latest but was played in by the player.
Any help gratefully received.