SQL Server: Count the number of times the ID from table A occurs in table B without using outer joins

Question

I asked here about counting the number of times a value from table a occurs in table b, but after it was solved, I realized I couldn't use a left join (or right or outer), since I'm going to use the results in an indexed view, which doesn't allow for those joins to be used.

So, if those restrictions apply, is there any way to do the following?

I have two tables: products and orders. Orders references products via ProductID as a foreign key. I want to know how many times each product has been sold, including the product never having been sold.

Is there a way to solve this that will have you ending up with something like this?

Product | Times sold
Milk    | 5
Bread   | 18
Cheese  | 0

Answer 1

There's no direct workaround - but you could construct an indexed view on Orders (so that the aggregates are being computed on it, and stored in an index), and have a non-indexed view based on a join between Products and Orders. You should still benefit from the index.

(Insert usual caveats re: using NOEXPAND, or working on Enterprise/Developer Edition)