Try to search your question here, if you can't find : Ask Any Question Now ?

SQL combinng the join result of two different table

HomeCategory: stackoverflowSQL combinng the join result of two different table
rupesh asked 2 weeks ago

I am having a problem with SQL at the moment

I use an example database of
W3School

This is what I have now:

SELECT Categories.CategoryID, Categories.CategoryName, Count(Products.CategoryID) as CountProduct
FROM Categories
INNER JOIN Products On Products.CategoryID = Categories.CategoryID
Group By CategoryName
ORDER BY CategoryName ASC

Output

CategoryID  CategoryName    CountProduct
-----------------------------------------
1           Beverages           12
2           Condiments          12
3           Confections         13
4           Dairy Products      10
5           Grains/Cereals      7
6           Meat/Poultry        6
7           Produce             5
8           Seafood             12

And This

SELECT Categories.CategoryID, Categories.CategoryName, Count(OrderDetails.ProductID) as CountOrder
FROM Categories
LEFT JOIN Products On Products.CategoryID = Categories.CategoryID
LEFT JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID
Group By CategoryName
ORDER BY CategoryName ASC

OUTPUT

CategoryID  CategoryName    CountOrder
------------------------------------------------
1           Beverages           93
2           Condiments          49
3           Confections         84
4           Dairy Products      100
5           Grains/Cereals      42
6           Meat/Poultry        50
7           Produce             33
8           Seafood             67

I just want this result

CategoryID  CategoryName    CountProduct    CountOrder
-------------------------------------------------------
1           Beverages           12              93
2           Condiments          12              49
3           Confections         13              84
4           Dairy Products      10              100
5           Grains/Cereals      7               42
6           Meat/Poultry        6               50
7           Produce             5               33
8           Seafood             12              67

I tried to combine two query into one but I get the wrong result.
Thanks you

1 Answers
Best Answer
naveen answered 2 weeks ago
Your Answer

9 + 5 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com