SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    Code:
    id |  tinyint(250)
    areaCode | varchar(3)
    I also have another table called 'agentToPostcode' as follows:

    Code:
    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?

    Code:
    $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;
    
    
    }
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    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

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! That is a very impressive query!

    Many thanks

    Paul
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •