Combining two sql querys

[smith.james0]

I have had a good look on google and a few evening trying to get it to work, but I am no further. I have two querys

 

The first is to get the all the information for the page from db main

SELECT *  FROM main WHERE Category = '$sql_Category' ORDER BY  `main`.`Name` ASC LIMIT $page, $limit

and the second is to get the rating for the first 

SELECT `item`, (`totalrate` / `nrrates`) AS `rank` FROM `rtgitems` ORDER BY (`totalrate` / `nrrates`) DESC 

The item column in rtgitems uses the id from the main table as a reference between the two.

 

I would like to sort the result in the first query by ranking score taken from the second table like

 

SELECT * FROM main WHERE Category = '$sql_Category' ORDER `item`, (`totalrate` / `nrrates`) AS `rank` FROM `rtgitems` ORDER BY (`totalrate` / `nrrates`) DESC

 

can this be done?

 

James

 

 

[Psycho]

SELECT main.*, rtgitems.item, (rtgitems.totalrate / rtgitems.nrrates) AS rank


FROM main

JOIN rtgitems ON rtgitems.item = main.id


WHERE main.Category = '$sql_Category'


ORDER BY main.`Name` ASC, rtgitems.totalrate / rtgitems.nrrates) DESC


LIMIT $page, $limit

Edited May 21, 2013 by Psycho