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?
Query to get number of related records in 2 child tables
sql;sql server
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 PencilsCountFROM    Brands