MySQL query problem

Question

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

I can see how you would relate Products, Sales, and Links, but what column links Category in with these? — T.J. Crowder
– T.J. Crowder, Commented Dec 13, 2010 at 7:37
@I-M-JM: for reference, SQL JOINs, GROUP BY and aggregate functions. — outis
– outis, Commented Dec 13, 2010 at 8:08

DrColossos · Accepted Answer · 2010-12-13 08:01:13Z

1

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

answered Dec 13, 2010 at 8:01

DrColossos

13.1k3 gold badges49 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I-M-JM Over a year ago

by the way, I replaced inner join to left join, and it helped.

Collectives™ on Stack Overflow

MySQL query problem

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related