Join 2 Lookup Tables to a Detail table

I have 3 tables:

• Products


• Groups


• Sales

The products table contains the following information:

|**Product ID**|**Product Description**|

|--------------|-----------------------|

|1             |Wine                   |

|2             |Ruler                  |

|3             |Gas                    |

|4             |Water                  |

The Groups table contains the following information:

|**Group ID**|**Group Description**|

|------------|---------------------|

|1           |Cheetahs             |

|2           |Elephants            |

|3           |Cougars              |

The Sales table contains the following information:

|**GroupID**|**Product ID**|**Amount Sold**|**Day Sold**|

|-----------|--------------|---------------|------------|

|1          |2             |              3|07-31-2016  |

|1          |1             |              1|07-31-2016  |

|2          |3             |              5|07-31-2016  |

|1          |4             |              2|08-01-2016  |

Now I have to produce a query that could bring me a result set as follows (with the condition that I want only results from 07-31-2016):

|**Group ID**|**Product ID**|**Amount Sold**|

|------------|--------------|---------------|

|1           |1             |1              |

|1           |2             |3              |

|1           |3             |0              |

|1           |4             |0              |

|2           |1             |0              |

|2           |2             |0              |

|2           |3             |5              |

|2           |4             |0              |

|3           |1             |0              |

|3           |2             |0              |

|3           |3             |0              |

|3           |4             |0              |

I thought this was going to be just a matter of using left joins, but it appears it wouldn't bring me back the result I was looking for (I don't want to omit products nor groups which weren't sold).

So in summary, I need to display all groups and all products no matter if they had an appearance in the Sales table.

I would appreciate any feedback on this matter, directions on where to look at or any logic that I may be missing!

---

EDIT

I've marked Matt's (big thanks) post as the answer, turns out I've never used a cross join.

I only added the where clause inside the left join of the Sales table in order to get just the sales made on 07-31-2016

SELECT

    g.GroupId

    ,p.ProductId

    ,SUM(COALESCE(s.AmountSold,0)) as AmountSold

FROM

    Products p

    CROSS JOIN Groups g

    LEFT JOIN Sales s

    ON p.ProductId = s.ProductId

    AND g.GroupId = s.GroupId

    AND daySold = '07-31-2016'

GROUP BY

    g.GroupId

    ,p.ProductId

ORDER BY

    g.GroupId

    ,p.ProductId