Trying to JOIN two date columns with WHERE conditions

[NiallFH]

Good afternoon all, I'm looking for some help with joins and dates.  Not something I have ever done before so hoping that someone can point me in the right direction.  Here is the code I have just now which works absolutely fine in terms of find the person with the oldest date of birth:

$get_oldest_player = mysql_query("
SELECT 
	P.PlayerDOB AS dob,
	CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, 
	P.PlayerID AS id
FROM 
	tplss_players P, tplss_appearances A, tplss_matches M
WHERE 
	P.PlayerID = A.AppearancePlayerID AND
	A.AppearanceMatchID = M.MatchID AND
	M.MatchDateTime = '$matchdate'
ORDER BY
	dob ASC
LIMIT 0,1
",$connection) or die(mysql_error());

Problem is, I have two tables I'm wanting to include in this query, and not entirely sure how I combine them in the search.  I have ONE table for the people (tplss_players) but TWO tables recorded instances of their involvement in matches (tplss_appearances and tplss_substitutions). 

 

Should I use a LEFT JOIN somehow ?  Not sure where it sits in the layout of the query?

 

tplss_appearances and tplss_substitutions are the same in their structure.  

 

Any help would be greatly appreciated.

 

Niall

[Barand]

You appear to have a structure like this

+--------------+                                         +--------------+
|  player      |                                         |    match     |
+--------------+                                         +--------------+
        |                                                      |
     playerid                                               matchid
        |                  +-------------------+               |
        +-----------------<|   appearances     |>--------------+
        |                  +-------------------+               |
        |                                                      |
        |                                                      |
        |                  +-------------------+               |
        +-----------------<|  substitutions    |>--------------+
                           +-------------------+

so you going to need all the players (from appearances and substitutions) that were involved in the match. This will require a UNION and not a JOIIN

(You could consider combining these table into one table with an extra column denoting Appearance or Substitution)

SELECT 
    P.PlayerDOB AS dob,
    CONCAT(P.PlayerFirstName, ' ', P.PlayerLastName) AS name, 
    P.PlayerID AS id
FROM 
    tplss_players P
    INNER JOIN
        (
            SELECT AppearancePlayerID as PlayerID
            , AppearanceMatchID as MatchID
            FROM tplss_appearances
            UNION
            SELECT SubstitutionPlayerID as PlayerID
            , SubstitutionMatchID as MatchID
            FROM tplss_substitutions 
        ) 
        as total USING (PlayerID)
    INNER JOIN tplss_matches M USING (MatchID)
WHERE 
    M.MatchDateTime = '$matchdate'
ORDER BY
    dob ASC
LIMIT 0,1

Edited May 22, 2014 by Barand

[NiallFH]

Thank you very much, this works perfectly!