sql – how to count rows in sub-query according main query items

I’m trying to get a list like this:

StockPart.title | qtyAvailable

qtyAvailable is the SUM() of stockItems rows where stock_items.stock_part_id = stock_parts.id (main query)

By using something like this:

SELECT 
  stock_parts.id,
  stock_parts.title,
    COUNT(*) FROM (SELECT id FROM stock_items WHERE stock_part_id = <STOCK_PART_ID_HERE> ) AS qtyAvailable
FROM
  `stock_parts`,
  `stock_items`
WHERE 1

How can I relate the WHERE clause in the sub-query to the main query row?

Answer

SELECT sp.id
       , sp.title
       , COUNT(si.stock_part_id ) AS qtyAvailable
FROM stock_parts sp 
left JOIN stock_items si
ON si.stock_part_id = sp.stock_part_id
GROUP BY sp.id, sp.title;

Here is a small demo