Left Joins and Greatest Function?

[ShoeLace1291]

So I have a query that pulls info from three different tables.  The first table is clan matches, second is match players, and third is members.  Clan matches is the main table for the query.  Match players has a column called matchID, so this table keeps track of each member that played in each match.  Then the third join selects from members which gets their username. 

 

The players table has a column called expEarned, and I would like to get the row containing the highest integer value from that column equal to the appropriate match ID.

 

I tried out the GREATEST function but I got an SQL error that says this:

Incorrect parameter count in the call to native function 'GREATEST'

 

I've never used the "greatest" function but based on the MYSQL manual, it should be the function I am looking for, right?  I guess there's more to it than calling the function with the table column as the parameter.  Here's my code:

 

$recent = db_query("
	SELECT m.ID_MATCH AS ID_MATCH, m.opponentName AS opponentName, m.roundWins AS wins, m.roundLosses AS losses,
		m.kills AS kills, m.losses AS losses, mp.ID_MEMBER, mp.expErned AS mvpEXP,
		mem.ID_MEMBER AS mvpID, mem.realName AS mvpName
	FROM {$db_prefix}clan_matches AS M
		LEFT JOIN {$db_prefix}clan_match_players AS mp ON (GREATEST(mvpEXP))
		LEFT JOIN {$db_prefix}members AS mem ON (mvpID = mp.ID_MEMBER)
	WHERE m.scheduledTime < ".time()."
	ORDER BY m.ID_MATCH DESC LIMIT 10", __FILE__, __LINE__);

 

If you haven't figured it out, I'm making a mod for SMF... lol.

[Adam]

I don't think you're using the right function to be honest. greatest() requires multiple values in order to return the greatest out of them, you're just passing a single value.

 

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.

 

Within the join 'on' clause you need to specify how to join `clan_matches` to `clan_match_players`, not the condition. Also you wouldn't use a LEFT JOIN here as that would join the data regardless, you need use a normal JOIN and specify the condition on which to join the tables within the where clause.