Trouble with Query construction

[Mr_Mako2]

I'm attempting to create a query that will grab the COUNT of a list of values in a table, and then display the one with the least returns. I'm working with two tables, and I've been struggling with this for the last day or so without any results.

For my example the two tables I'm working with are :

|---------------|         |------------------------|
|    Table1     |         |       Table2           |
|---------------|         |------------------------|
| Id     Squad  |         | Id     Squad   Status  |
|---------------|         |------------------------|
| 1      200th  |         | 1        1       1     |
| 2      201st  |         | 2        1       3     |
| 3      202nd  |         | 3        1       2     |
| 4      203rd  |         | 4        1       1     |
|---------------|         | 5        1       3     |
                          | 6        1       4     |
                          | 7        1       1     |
                          | 8        2       2     |
                          | 9        2       1     |
                          | 10       2       1     |
                          | 11       2       3     |
                          | 12       3       1     |
                          | 13       3       3     |
                          | 14       3       1     |
                          | 15       3       3     |
                          | 16       3       4     |
                          | 17       3       1     |
                          | 18       4       1     |
                          | 19       4       1     |
                          | 20       4       3     |
                          | 21       4       2     |
                          | 22       4       4     |
                          | 23       4       1     |
                          | 24       4       3     |
                          |------------------------|

The Table1.Id corresponds to Table2.Squad. Each "squad" can only have 4 active members at any time - the "status" in Table2 displays each member's status (1 and 2 are active, anything higher indicates an inactive status).

So, looking at Table2 above -

Squad 200th has 4 active members (three 1 and one 2 status) and is full.
Squad 201st has 3 active members (two 1 and one 2 status), and has one open position.
Squad 202nd has 3 active members (three 1 and one 2 status), and has one open position.
Squad 203rd has 4 active members (three 1 and one 2 status) and is full.

What I need to do is construct the query so that it searches Table2 for the COUNT of each squad and whether it's less than 4, and the first squad that is less than 4 is displayed as the result - which in this case would be Squad 201st.

I've tried using COUNT in conjunction with MAX, and that didn't work. The last thing I've tried is :

$sql = "SELECT COUNT(table2.id) as Squad, table1.squad as Name
        FROM table2
        LEFT JOIN table1 ON (table2.squad = table1.id)
        WHERE table2.status <= '2'
        GROUP BY Name";
$result = @mysql_query($sql, $connect) or die(mysql_error());

... but that wasn't successful.

I'm fairly sure that what I'm trying to do isn't that difficult to construct at all, but I'm not having any luck looking through the forums to find anything related, or trying to find a solution through trial and error.

Thanks in advance for any tips or assistance.

[Shrike]

select t1.Name, count( t2.Id ) from Table1 t1
inner join Table2 t2 on t1.Id = t2.Squad
where t2.Status <= 2
group by t2.Status having count(t2.Id) < 4
order by Name

Something like that anyway 🙂