SQL request 3 linked tables

Question

I have 3 Tables:

And i want this result:

I try with order by but, it did not work, Someone have an idea

Answer 1

You can do an INNER JOIN with GROUP BY, e.g.:

SELECT s.number AS supplier, c.category AS category, s.date as `date`, COUNT(r.*)
AS total
FROM supplier s JOIN register r ON s.id = r.supplier_id
JOIN category c ON c.id = r.category_id
GROUP BY s.number, c.category, s.date;

Answer 2

Assuming the Linked Server name is [LinkedSample\Tables] Then you can use the code below:

SELECT s.number AS supplier, c.category AS category, s.date as `date`, COUNT(r.*) AS total
FROM  [LinkedSample\Tables].supplier s 
JOIN [LinkedSample\Tables].register r ON s.id = r.supplier_id 
JOIN [LinkedSample\Tables].category c ON c.id = r.category_id
GROUP BY s.number, c.category, s.date;