Join 2 Lookup Tables to a Detail table

Question

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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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 s.daySold = '07-31-2016'
GROUP BY
    g.GroupId
    ,p.ProductId
ORDER BY
    g.GroupId
    ,p.ProductId

Note your expected results you provided are wrong for group 1 product 4 there were 2 of those in the sale.

Join 2 Lookup Tables to a Detail table

EDIT

Sign up