I have 2 database tables
- directory
- 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
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
0 comments:
Post a Comment