Joining two tables for display date ordered rows

NiallThistle

I have two tables, which both contain events stored by date.

What I want to do is display rows of results from these, in date order - but I want to combine both tables and display them in the same set of results.

My table information:

Table 1:
date
appearanceid

Table 2:
date
halfappearanceid

In the context of the tables, appearanceid and halfappearanceid are the common field. So, my result will display a list of appearanceid and halfappearanceid results in date order, together.

If that makes sense, can anyone help?

laserlight

What have you tried?

NiallThistle

Been going round in circles really.

	$query = mysql_query("
	SELECT M.MatchID AS id,
	DATE_FORMAT(M.MatchDateTime, '%M %D, %Y') AS time,
	O.OpponentName AS opponent,
	O.OpponentID AS oppid
	FROM tplss_matches M, tplss_opponents O, tplss_appearances A, tplss_substitutions S
	WHERE (M.MatchID = A.AppearanceMatchID OR  M.MatchID = S.SubstitutionMatchID) AND
	(A.AppearancePlayerID = '$id' OR S.SubstitutionPlayerIDIn = '$id') AND
	M.MatchOpponent = O.OpponentID
	ORDER BY M.MatchDateTime ASC
	LIMIT 1
	",$connection)
	or die(mysql_error());

That's my query thus far, A.AppearancePlayerID and S.SubstitutionPlayerIDIn being the fields I want to combine in the results.

Weedpacket

Sounds more like a UNION rather than a join is called for.

NiallThistle

This isnt something I've ever used. Do you have a short example I could follow?

bradgrafelman

Because you want to do some combining and then do the ordering, I think you'll want a UNION inside of a subquery. Something like:

SELECT date_column, column1 event_column
# Aliasing column1 since UNION'd result sets take the column names
# from the first query in the UNION
FROM
(
    (
        SELECT date_column, column1
        FROM table1
    )
    UNION
    (
        SELECT date_column, column2
        FROM table2
    )
)
ORDER BY date_column