开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜