查询从不同的表中计算父类别下的项目

Question

I have stockcards and stockcategories table.

I want to display categories and show how many stockcards is openned under this category.

SELECT a.stockCatID, a.stockCatName, f.nProductsInside 
FROM stockcategories a 
LEFT JOIN 
        (
            SELECT COUNT(b.stockID) AS nProductsInside 
            FROM stockcards b 
        ) 
        f ON a.stockCatID = f.stockCatID 

Well it returns #1054 - Unknown column 'f.stockCatID' in 'on clause' Obviously I am making a mistake..

Answer 1

SELECT a.stockCatID, a.stockCatName, COUNT(b.stockID) as nProductsInside 
FROM stockcategories a 
LEFT JOIN stockcards b ON a.stockCatID = b.stockCatID 
group by a.stockCatID

You could probably do this too (unverified) ni order to stick with your query

SELECT a.stockCatID, a.stockCatName, f.nProductsInside 
FROM stockcategories a 
LEFT JOIN 
(
    SELECT COUNT(b.stockID) AS nProductsInside, stockCatID
    FROM stockcards b 
) 
f ON a.stockCatID = f.stockCatID