[SOLVED] ORDER BY

[EchoFool]

Is it possible order by an average from a field thats in a completely different table without an inner join?

 

Heres why im asking to see why the inner join is not a good choice if i can avoid it....

 

<?php
If($_GET['Sort'] == 1){
$Extra = 'ORDER BY GameName DESC';
}ElseIf($_GET['Sort'] == 2){
//some how .. order by AVG(Rating) FROM table "ratings" joined by GameID on ads table and ratings table...

}

$SELECT = mysql_querY("SELECT *
		FROM ads
		WHERE Authorised='1' AND Type='$Type' AND Status='$Status'
		$Extra")
Or die(mysql_error());
}
?>

 

Any ideas how i can do this ?

[kickstart]

Hi

 

Without doing a seperate piece of SQL with a JOIN (which would be my choice) the only thing I can think of would be to replace the select from ads with some kind of view which already joins ads and ratings, or to just always do the join but only sort by it when required.

 

Assuming GameId is a unique key on the ads table, something like this:-

 

<?php
If($_GET['Sort'] == 1)
{
$Extra = 'ORDER BY GameName DESC';
}
ElseIf($_GET['Sort'] == 2)
{
$Extra = 'ORDER BY GameAverageRating DESC';
}

$SELECT = mysql_querY("SELECT a.*, AVG(b.Rating) GameAverageRating
		FROM ads a
		LEFT OUTER JOIN ratings b
		ON a.GameId = b.GameId
		WHERE Authorised='1' AND Type='$Type' AND Status='$Status'
		GROUP BY a.GameId
		$Extra")
Or die(mysql_error());
}
?>

 

All the best

 

Keith

[EchoFool]

Thank you works a treat