CODEWITHSUNDEEP
phpmysqlsql
Sopheakdey Moeun
Sopheakdey Moeun

Reputation: 121

How to order the category that has most product

I'm not so clear in one of mysql. i have two tables:

tblcategory
id, name

tblproduct
id, Name, Qty, Price, Category_id

and when i use sql to select all catogories to order by product count: 

SELECT c.id,c.name,count(p.id) as product_count 
FROM tblcategory as c inner join tblproduct as p on c.id=p.category_id 
GROUP BY c.id,c.name 
ORDER BY product_count;

The result was that some category which has no product was not appear in my result! how can i get all of them?

Upvotes: 3

Views: 106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You need to use a left outer join:

SELECT c.id,c.name,count(p.id) as product_count 
FROM tblcategory as c left outer join tblproduct as p on c.id=p.category_id 
GROUP BY c.id,c.name 
ORDER BY product_count;

The inner join only keeps records that match in both tables. You want all the product categories, even when there are no matches. The left outer join keeps all records in the first table (tblcategory) along with any matching records in the second table (tblproduct). If there are no products on a category, then you'll get a value of 0 instead of a missing row.

Upvotes: 6

Related Questions