CODEWITHSUNDEEP
sqlmysql
I-M-JM
I-M-JM

Reputation: 15950

MySQL query problem

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

Upvotes: 0

Views: 62

Answers (1)

DrColossos
DrColossos

Reputation: 12988

SELECT c.Name AS "CategoryName", COUNT(p.ProductID) AS "TotalProducts", COUNT(s.SalesID) AS "TotalSales", COUNT(l.LinkID) AS "TotalLinks"
FROM Category c
INNER JOIN Products p ON p.CategoryID = c.CategoryID
INNER JOIN Sales s ON s.ProductID = p.ProductID
INNER JOIN Links l ON l.ProductID = s.ProductID;

I don't know how the data looks like but you may need to add a GROUP BY somewhere if you have duplicates or an optional ORDER BY if you need sorting in any of the columns

Upvotes: 1

Related Questions