multi-table mysql query
I have 4 tables
Table: Category
CategoryID (int)
Name (varchar)
Table: Products
ProductID (int)
CategoryID (int)
Name (varchar)
Description (text)
Table: Sales
开发者_运维技巧SalesID (int)
ProductID (int)
Table: Links
LinkID (int)
ProductID (int)
Now I need to display data as:
CategoryName Total Products Total Sales Total Links
ABC 5 12 50
XYZ 12 26 10
How can I achieve this, may be in single query
Help appreciated
Thanks
SELECT CategoryName, Count(distinct p.ProductId) TotalProducts, Count(distinct s.SalesId) TotalSales,
COUNT(distinct l.LinkId) TotalLinks
FROM Products p JOIN SALES s on p.ProductId = s.ProductId
JOIN Categories c ON c.CategoryId = p.CategoryId
JOIN Links l ON p.ProductId = l.LinkId
GROUP BY CategoryName
select
c.CategoryId,
c.name as CategoryName,
count(p.ProductId) as TotalProducts,
(select count(s.salesid) from sales s where s.ProductId = p.ProductId) as TotalSales,
(select count(l.linkid) from products l where l.ProductId = p.ProductId) as TotalLinks
from
Category c
left join Products p on p.CategoryId = c.CategoryId
group by
c.CategoryId,
c.Name
SELECT
CAT.Name CategoryName,
(SELECT COUNT(P.ProductsID) FROM Products P WHERE P.CategoryID=CAT.CategoryID) TotalProducts,
(SELECT COUNT(S.SalesID) FROM Sales S JOIN Products P ON S.ProductID=P.ProductID WHERE P.CategoryID=CAT.CategoryID) TotalSales,
(SELECT COUNT(L.LinkID) FROM Links L JOIN Products P ON L.ProductID=P.ProductID WHERE P.CategoryID=CAT.CategoryID) TotalLinks
FROM
CATEGORY CAT
精彩评论