Devesh Agrawal Devesh Agrawal - 4 months ago 8
MySQL Question

return product ids and their count even if there is no variant for them in mysql

i have 3 tables:

table_products - product_id, pname
variants - vid, vname
table_product_varients - product_id, vid


i want to get the count for product variants for all the products, and if there is no variant for a product it should give count as 0.

This is my query:

SELECT P.product_id, count(*) AS count
FROM table_product_varients AS PV
LEFT JOIN table_products AS P ON PV.product_id = P.product_id
GROUP BY P.product_id
ORDER BY P.product_id ASC


But this is not giving products for which there is no variants.

can any one help me on this?

Answer

You should have put the table_products table to the left. Also you should have counted PV.product_id.

SELECT 
P.product_id, 
count(PV.product_id) AS count
FROM table_products AS P 
LEFT JOIN table_product_varients AS PV ON PV.product_id = P.product_id
GROUP BY P.product_id
ORDER BY P.product_id ASC;

Note: For those products which don't have corresponding entry in table_products_varients table you will get NULL value of PV.product_id. Thus COUNT(NULL) actually returns 0