How to bring data from two tables that could/could not be present in one of them Background: I have two tables, let's call them "purchaseTransactions" and "Products", the first one, holds all the transaction made by product, and the second one has all my products available, let's see an example oh how the look:

PurchaseTransactions


PurchaseDate
ProductId
Quantity
Some other fields...


Products


ProductName
ProductNumber
Some other fields...


Basically what I want to obtain is the quantity sold by month and product from a specific timelapse, For instance:

Let's say we have the Products


Name: Product A, No: 01
Name: Product B, No: 02
Name: Product C, No: 03


For Purchase Orders


PurchaseDate: Jan/01/2018, ProductId: Product A, Quantity: 100
PurchaseDate: Jan/18/2018, ProductId: Product A, Quantity: 150
PurchaseDate: Feb/02/2018, ProductId: Product A, Quantity: 150
PurchaseDate: Feb/24/2018, ProductId: Product A, Quantity: 200
PurchaseDate: April/04/2018, ProductId: Product A, Quantity: 200
PurchaseDate: January/01/2018, ProductId: Product B, Quantity: 222
PurchaseDate: March/02/2018, ProductId: Product B, Quantity 250
PurchaseDate: May/04/2018, ProductId: Product B, Quantity: 333
PurchaseDate: May/20/2018, ProductId: Product B, Quantity: 666


An example of what I want is to bring ALL the products sold grouped by Year,Month, Product and Sum of its quantity. Something like this.

 Year Month, ProductId, QuantitySold
 -----------------------------------
 2018 Jan   Product A   250
 2018 Jan   Product B   222
 2018 Jan   Product C   0
 2018 Feb   Product A   250
 2018 Feb   Product B   0
 2018 Feb   Product C   0
 2018 March Product A   0
 2018 March Product B   0
 2018 March Product C   250
 .... etc


Notice that the query should return even the products that does not have any purchases in the specified month, this is what I have tried so far:

SELECT YEAR(PurchaseDate), MONTH(PurchaseDate), ProductId, SUM(Quantity)
FROM PurchaseOrders
WHERE Product IN (Product A, Product B...) AND MONTH(PurchaseDate) =&gt; DATEADD(MONTH, -3, GETDATE()) --Could be by some specific products and for a specific timelapse, ex the past three months.
GROUP BY YEAR(PurchaseDate), MONTH(PurchaseDate), ProductId


But this only returns the Products existing in the Purchase table, I have tried with a LEFT JOIN with the Products table to get all the products, but I think I am not implementing it correctly.

Any Idea of how to do this?
Try changing your GROUP BY YEAR(PurchaseDate)...
to GROUP BY ALL YEAR(PurchaseDate)...


  GROUP BY ALL -> "Specifies to include all groups in the results regardless of whether
  they meet the search criteria in the WHERE clause. Groups that don't
  meet the search criteria have NULL for the aggregation."


For some reason, MS decided to deprecate the feature, but it still works.
See the GROUP BY topic in the documentation.