raltandi raltandi - 2 months ago 15
SQL Question

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

Answer
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.