Mysql - LEFT JOIN with COUNT

Question

I have 2 database tables

1. directory
2. directory_category

They are linked by the category id (as cat_id & id) in both tables respectively

---

I want to list all the categories in directory_category and at the same time count how many records are found in directory for this category (using a single sql query)

I have tried

SELECT 
      directory_category.id
      directory_category.category_name
      directory.cat_id
      count(directory) as total_records
      FROM directory_category
      LEFT JOIN directory
      ON directory_category.id = directory.cat_id

This query only produces one record and the total_records seems to be the sum of the entire directory table

Answer 1

SELECT 
    directory_category.id,
    directory_category.category_name,
    directory.cat_id,
    COUNT(directory.id) AS total_records
FROM directory_category
LEFT JOIN directory ON directory_category.id = directory.cat_id
GROUP BY directory_category.id

Answer 2

At a guess (and a quick one), I'd say something like:

SELECT 
    directory_category.id,
    directory_category.category_name,
    directory.cat_id,
    count
FROM
    directory_category
LEFT JOIN
    (
        SELECT
            count(directory) as total_records
        FROM
            directory
        INNER JOIN
            directory_category
        ON
            directory_category.id = directory.cat_id
    ) AS count

But you'd need to play around with it, obviously.