I have a table which shows our top sellers and Weeks of Stock left. Its generated by joining an order db with a stock db. I'm struggling with try to capture the really fast movers.

Here is the script

SELECT 
a.EAN, 
a.SKU, 
a.Name, 
Sum(a.OrderedQuantity) 
Round(Avg(a.Price),2) as AvgPrice
b.Quantity, 
round((b.Quantity/Sum(a.OrderedQuantity)),0) as WOS
FROM orders a 
inner join stock b on a.sku = b.sku 
Where a.condit =  "New"
And a.EAN <> ""
and b.sku
and WeekofYear(OrderDate) between ' . $strtwk . ' and ' . $endwk . '
Group by EAN
Limit 0, 50

The issue is that if there is no stock left (we've sold out in that week) then the innerjoin statement fails as zero value sku's are removed from the stock db.

I was thinking of running 2 queries (with and without matching the stock.sku and then seeing the exceptions i.e. top sellers with no stock.

Is there a more effective way of finding the top 50 both with and without stock?

Use a LEFT JOIN. You can then in your SELECT check for NULL values (missing SKU's) and return 0 (if that is what you want).

Brilliant. That did it.

Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.