Can I Query or Do I Need a Table Function?

I have data that represents store locations and products. Most stores use the price in the product table, but some stores override that price. They will have a record in the LocalPrices table. Currently, I can get the data that I need with a function, but I'm having trouble getting it w/ a query:

SELECT *
FROM Location loc
LEFT JOIN LocalPrices lp ON loc.locationId = lp.locationId
JOIN Products prod ON lp.productId = prod.productId
WHERE loc.locationId = <the store I'm filtering on>

If there is no location specific price in the LocalPrices table, the join doesn't work. I'm fine using a function, but I was wondering if I'm missing something in the query or a better table schema might help.

Thanks.

Perhaps:

SELECT *
FROM Location loc
CROSS JOIN Products p
LEFT JOIN lp ON loc.locationId = lp.locationIdprod 
WHERE loc.locationId = 

Cross join ensures all locations have all products. then the left join shows where a price override exists at a specific location.

Note cross joins can be expensive but if you're limiting it to one store then the optimizer should only need to generate the cross join for just that location.

I look at it as each location can have all the products as the "Default" and then that location can optionally override a product at a location level. Put another way: products are company wide for all locations; while localPrices could override, exclude, or apply other location specific product details.

You could also use coalesce to provide the price from location prices if it exists, otherwise price from products)

SELECT coalesce(lp.price,p.price) as UnitPrice
FROM Location loc
CROSS JOIN Products p
LEFT JOIN lp ON loc.locationId = lp.locationIdprod 
WHERE loc.locationId =