# Query to get number of related records in 2 child tables

I have a category table (brands) and 2 other tables (Pens and Pencils) that have parent-child relation with this table through a field CatID. Now I want to get a list of Brandsand number of records in child tables.

I use this query:

Select
B.* ,COUNT(P.ID) as PensCount, COUNT(Pc.ID) as PencilsCount
from
Brands B
left outer join
Pens P on B.ID=P.CatID
left outer join
Pencils Pc on B.ID= Pc.CatID
group by
B.ID,B.Title;


Is this correct? Is there any way to make this query better?

## 1 Answer

Did you try this query? Did it work? Unless ID and Title are the only columns in the Brands table this query would result in an error.

I would use subqueries for this:

SELECT
Brands.*,
(SELECT COUNT(*) FROM Pens WHERE CatID = Brands.ID) AS PensCount,
(SELECT COUNT(*) FROM Pencils WHERE CatID = Brands.ID) AS PencilsCount
FROM
Brands