How would I write this 3 level deep mysql query?

mysql, sql

Solution

SELECT Categories.Name,count(DISTINCT categories.name) FROM Categories
JOIN Domains ON Categories.ID=Domains.CID
JOIN Records ON Records.DID=Domains.ID
GROUP BY Categories.Name

Tested with following setup:

CREATE TABLE Categories (Name nvarchar(50), ID int  NOT NULL IDENTITY(1,1))
CREATE TABLE Domains (Name nvarchar(50), ID int NOT NULL IDENTITY(1,1), CID int)
CREATE TABLE Records (Name nvarchar(50), ID int NOT NULL IDENTITY(1,1), DID int)

INSERT INTO Records (DID) VALUES (1)
INSERT INTO Records (DID) VALUES (1)
INSERT INTO Records (DID) VALUES (2)
INSERT INTO Records (DID) VALUES (2)
INSERT INTO Records (DID) VALUES (3)
INSERT INTO Records (DID) VALUES (3)

INSERT INTO Domains (Name,CID) VALUES ('D1',1)
INSERT INTO Domains (Name,CID) VALUES ('D2',1)
INSERT INTO Domains (Name,CID) VALUES ('D5',1)
INSERT INTO Domains (Name,CID) VALUES ('D3',2)
INSERT INTO Domains (Name,CID) VALUES ('D4',2)

INSERT INTO Categories (Name) VALUES ('1')
INSERT INTO Categories (Name) VALUES ('2')
INSERT INTO Categories (Name) VALUES ('3')

Problem

This might be a little hard to explain, but I will try. I want to display a list of categories (stored in 1 table), and number of domains associated with each category (stored in another table). The monkey wrench in this case is that each domain has a set of records associated with it (which are stored in a 3rd table). I only want to show the categories that have domains associated with them, and the count of domains should reflect only the domains that have records associated with them (from the 3rd table). My current query ``` SELECT r.rev_id, c.cat_id, c.cat_name, count(d.dom_id) As rev_id_count FROM reviews r INNER JOIN domains d ON r.rev_domain_from=d.dom_id INNER JOIN categories c ON d.dom_catid=c.cat_id WHERE rev_status = 1 GROUP BY cat_name ORDER BY cat_name ``` This selects the correct category names, but shows a false count (rev_id_count). If the category has 2 domains in it, and each domain has 2 records, it will show count of 4, when it should be 2.

Original source