How to rewrite a sub-query

[lonestarbill]

I use this query against an Access database and need to convert it to MySQL. It just does a quick count of active products in each category, and it seems overkill to create temp tables to do this. Is there a way to re-write this using joins? I'm not very experienced with SQL, and everything I've tried hasn't worked out. Thanks!

SELECT count(idProd) as CountCatItems FROM cat_prod WHERE idCat=14 and (idProd in (SELECT idProd from Prods WHERE prods.active=-1))

 

[piti]

try this one

SELECT COUNT(cat_prod.idProd) AS CountCatItems
FROM cat_prod INNER JOIN Prods
ON Prods.idProd = cat_prod.idProd
WHERE Prods.active=-1

 

[lonestarbill]

Thanks, Piti!