List all records from one table with total for each from another

Databases
1

I have a table of postcode prefixes called ‘postalAreas’ as follows:



id |  tinyint(250)
areaCode | varchar(3)

I also have another table called ‘agentToPostcode’ as follows:



areaCode | varchar(3)
agentId | tinyint (250)

The first table has a full list of 124 area codes.

When an agent is added to the database, the “agentToPostcode” table is updated with the area code for this agent.

So what I want to do is display a list of all 124 area codes and next to each to display how many of each agent there are assigned to it.

I suspect the following code would do this however it seems really inefficient and I would like to know if there is a solution to do this with just one query?



$query="SELECT * FROM postalAreas ORDER BY areaCode";					

$result = @mysql_query ($query); // Run the query

while ($r = mysql_fetch_array($result)) {


	$areaCode=$r["areaCode"];	

	$query2="SELECT * FROM agentToPostcode WHERE areaCode = $areaCode";

	$result2 = @mysql_query ($query2); 

	$num_rows = mysql_num_rows($result2);	

	echo "Area Code " . $areaCode . " Total: " . $num_rows;


}
2

Wow! That is a very impressive query!

Many thanks

Paul

3 

SELECT 
    p.areaCode
  , COALESCE(a.numberOfAgents, 0) AS numberOfAgents
FROM postalAreas AS p
LEFT OUTER JOIN 
  (SELECT 
       areaCode
     , COUNT(*) as numberOfAgents
   FROM agentToPostcode
   GROUP BY areaCode
  ) AS a
ON p.areaCode = a.areaCode
ORDER BY p.areaCode