We help IT Professionals succeed at work.
Get Started

Database Query Assistance

highlawn
highlawn asked
on
279 Views
Last Modified: 2012-05-12
I have a database that manages items for a sports club. There are the following tables present that I wish to query:

Member - has an autonumber ID and contains name details and other personal info
Player - 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.

Selection - 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.

Fixture - 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=fixture.id

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

WHERE p.member_id=m.id 
AND (m.date_of_birth<"1993-09-01" OR date_of_birth="0000-00-00") 
AND m.gender="male"  

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, 
m.active DESC, 
f.date DESC

Open in new window


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.
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 17 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE