A few points ...
Unless you use joins with Made_Up_Of and Season to filter rows, you do not need these tables. I left them here; you can add them back if you need them.
Mark Tickner is correct that you should use the ANSI JOIN syntax. The nice thing (besides the standard one) is that it correctly connects the join logic to the joined table. Once you get used to it, I think you will find it preferable.
What you really need is the maximum pf.StartDate for each PlayerID , which is suitable for the ROW_NUMBER() analytic function. PARTITION BY pi.PlayerID ORDER BY pf.StartDate DESC will basically assign a value to 1 row with each last sort date of each player. An external filter selects all rows except those that have a rating of 1 .
You can also assign ranking to the analytical functions RANK() and DENSE_RANK() , but if a player has a binding to the most recent date, then all the linked dates will be ranked # 1, and you will get several rows for this player. In such situations, when you want only one row per player, use ROW_NUMBER() instead.
Put it all together and you get the following:
SELECT MatchID, PlayerID, TeamID, MatchDte, StartDate FROM ( SELECT pi.MatchID, pi.PlayerID, t.TeamID, m.MatchDate, pf.StartDate, ROW_NUMBER() OVER (PARTITION BY pi.PlayerID ORDER BY pf.StartDate DESC) AS StartDateRank FROM Plays_In pi INNER JOIN Match m ON pi.MatchID = m.MatchID INNER JOIN Plays_A pa ON m.MatchID = pa.MatchID INNER JOIN Team t ON pa.TeamID = t.TeamID INNER JOIN Plays_For pf ON pf.PlayerID = pi.PlayerID AND pf.TeamID = t.TeamID WHERE pi.MatchID = '&match_id' AND m.MatchDate >= pf.StartDate ) WHERE StartDateRank = 1 ORDER BY MatchID, PlayerID
One final point: based on WHERE pi.MatchID = '&match_id' it looks like you can use PHP as a front end and mysql function to execute a query. If so, check out mysqli or PDO instead, as they will protect you from SQL Injection. mysql functions (which are officially deprecated) will not.
Addendum : More information on ROW_NUMBER , thanks in large part to @AndriyM.
With ROW_NUMBER , if a player has more than one row with the most recent date, only one row will be assigned as ROW_NUMBER = 1 , and this row will be selected more or less randomly. Here is an example when the player’s last date is 5/1/2013, and the player has three lines with this date:
pi.MatchID pi.PlayerID pf.StartDate
Please note that only one of the above lines will be assigned ROW_NUMBER = 1 , and this can be any of them. Oracle will decide, not you.
If this uncertainty is a problem, order additional columns to get a clear gain. For this example, the highest pi.MatchID will be used to determine the "true" ROW_NUMBER = 1 :
-- replace `ROW_NUMBER...` in the query above with this: ROW_NUMBER() OVER ( PARTITION BY pi.PlayerID ORDER BY pf.StartDate DESC, pi.MatchID DESC) AS StartDateRank
Now, if there is a relationship for the highest pf.StartDate , Oracle is looking for the highest pi.MatchID in a subset of the rows with the highest pf.StartDate . As it turned out, only one line satisfies this condition:
pi.MatchID pi.PlayerID pf.StartDate