Complex sort & count

[Anon]

First of all, this thing seems to me pretty complex: many thanks in advance to anyone who can help.
I'm using two MySQL tables, one with two columns (team name and league ID), and one with 4 columns (league ID, league name, max number of teams and beginning date).
I need to sort these data respecting these rules:
1) Leagues with the least difference between number of partecipating teams and max number of teams must be on top of selection (thus, leagues that have already reached max number of applications will be put on the bottom)
2) Can't select and count(*) teams using LEFT JOIN, as there can be leagues with no teams subscribed (but still have to show up so that teams can apply)
3) User must have the chance to see only leagues beginning after or before a certain date
4) Results must be split on a number of pages (so with offset and limit)
Right now the best I can achieve is a LEFT JOIN that can't respect rule #1 (I can only sort ASC or DESC by number of teams per league) and rule #2 (leagues with no teams won't show up)
Anyone can lend a hand in fixing this? Any help will be very very appreciated as this thing is really giving me a headache.

[Anon]

i don't think it would be that hard to do, unless im not understanding... for each league ID count how many teams it has connected. next, make an array that holds the league id and the number of teams connected, then sort by the number of teams connected. then reload your database in the order of your new sorted arrays and load it by the league id (eg. if you have a league with 3 and a league with 5, the array will be something like:

$team_array("leagueID", "TeamNums")
$team_array("12345", 3)
$team_array("67890", 5)

that way you load 12345 before you load 67890 and hence your teams are now displayed in order where the most teams in the league is at the bottom of the page. I'm hoping from now you can sort by date, the easiest way would be to convert the date to a timestamp and sort it then convert it back, displaying the results on multiple pages isn't hard either just when you make you loop to display all the results in a table or whatever, make the loop only go up to the number of results you want per page. If you have any questions just email me.

[Anon]

Thank you for your help. Since I needed it to be a single query I added a column in the league table and update there the count of partecipating teams every time a team joins the league. Not the best solution I wanted, but since I can\\'t get temporary table to work and I need a single query not to change the huge code that is forming the frontend I went for that. Thank you again.