How do I write this 3-level mysql query?

Question

It may be a little difficult to explain, but I will try.

I want to display a list of categories (stored in 1 table) and the number of domains associated with each category (stored in another table).

In this case, the monkey’s key is that each domain has a set of records associated with it (which are stored in the third table). I want to show only those categories that have domains associated with them, and the number of domains should reflect only domains that have records associated with them (from the third table).

My current request

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

The correct category names are selected here, but a false value is displayed (rev_id_count). If there are 2 domains in a category and there are 2 entries in each domain, it will display a counter of 4, if it should be 2.

Answer 1

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

:

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')

Answer 2

select c.name, count(distinct d.did) from domains d
  left join categories c on c.cid = d.cid
  left join records r on r.did = d.did
  group by c.name

2 , 2 , . :

name     count
----     -----    
test     2
test2    2

Answer 3

- ?

SELECT c.name, count(d.id)
FROM categories c
JOIN domains d ON c.id = d.cid
JOIN records r ON r.did = d.id
GROUP BY c.name;

Answer 4

, , , .

-

 SELECT * FROM records 
    INNER JOIN domains on <clause> 
    INNER JOIN categories on <clause>
 WHERE <something>

, SQL , , , , , . , , ( ).

Answer 5

AquilaX, :

SELECT c.name, d.name, count(d.id)
  FROM categories c
    JOIN domains d ON c.id = d.cid
    JOIN records r ON r.did = d.id
GROUP BY c.name, d.name;

:

Cat 1, Domain 1, 2
Cat 1, Domain 2, 1
Cat 2, Domain 3, 5

....

( )