CODEWITHSUNDEEP
sqlsql-server
Jason
Jason

Reputation: 23

Joining to return all rows even when there are no results

I'm trying to get a list how many products we've sold, per company, by all categories.

I'd like the results to look like:

Category A  Company 1    0
Category A  Company 2    0 
Category A  Company 3    5
Category B  Company 1    1 
Category B  Company 2    4
Category B  Company 3    0

So every category is returned, every company is returned, even if there are no sales.

This is the query I'm trying and it should make the structure of the database clear. I've attacked this from a bunch of direction, but can't seem to wrap my head around how to get at what I'm looking for. 

SELECT com.Company_Name, c.Category_Name,  sum(p.Quantity) 
FROM Category c
LEFT JOIN Item i on c.Category_ID = i.Category_ID
LEFT JOIN Products p on p.Item_ID = i.Item_ID
LEFT JOIN Invoice iv on iv.Invoice_ID = p.Invoice_ID
LEFT JOIN Company com on com.Company_Id = iv.Company_ID
group by c.Category_Name, com.Company_Name

Thanks for any help...

Upvotes: 2

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Generate the rows with a cross join, then left join in the rest of the information:

SELECT co.Company_Name, ca.Category_Name, 
       COALESCE(SUM(p.Quantity), 0) as quantity 
FROM Category c CROSS JOIN
     Company co LEFT JOIN
     (Invoice iv JOIN
      Products p
      ON iv.Invoice_ID = p.Invoice_ID JOIN
      Item i
      ON p.Item_ID = i.Item_ID
     ) 
     ON co.Company_Id = iv.Company_ID AND
        c.Category_ID = i.Category_ID
GROUP BY ca.Category_Name, co.Company_Name

Upvotes: 1

Related Questions