MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Category (id INT, name VARCHAR(64), deleted_at DATE);
INSERT INTO Category VALUES
( 1,'Food'      ,      NULL),
( 2,'Stationery',      NULL);

CREATE TABLE Product (id INT, cate_id INT, vendor_id INT, name VARCHAR(64), deleted_at DATE);
INSERT INTO Product VALUES
( 1,      1,        1,'Bread' ,      NULL),
( 2,      1,        2,'Milk'  ,      NULL),
( 5,      2,        2,'Pencil',      NULL);

CREATE TABLE Vendor (id INT, name VARCHAR(64), deleted_at DATE);
INSERT INTO Vendor VALUES
( 1,'Woolies',      NULL  ),
( 2,'Cole'   ,'2020-01-18');

SELECT * FROM Category;
SELECT * FROM Product;
SELECT * FROM Vendor;


id
name
deleted_at


1
Food
null

2
Stationery
null




id
cate_id
vendor_id
name
deleted_at


1
1
1
Bread
null

2
1
2
Milk
null

5
2
2
Pencil
null




id
name
deleted_at


1
Woolies
null

2
Cole
2020-01-18


SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product
FROM Category c
LEFT JOIN ( Product p 
            INNER JOIN Vendor v ON v.id = p.vendor_id AND v.deleted_at IS NULL 
            ) ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;


cate_id
cate_name
total_product


1
Food
1

2
Stationery
0


SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product
FROM Product p 
INNER JOIN Vendor v ON v.id = p.vendor_id AND v.deleted_at IS NULL 
RIGHT JOIN Category c ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;


cate_id
cate_name
total_product


1
Food
1

2
Stationery
0