2
\$\begingroup\$

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?

\$\endgroup\$
3
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.