SQL to find list of stores in city X that carry product Y and order by the total num of orders in asc

I've got these tables:

Stores (id, na开发者_如何转开发me, city)
Products (id, name, price)
StoresProducts (id, store_id, product_id)
Orders (id, customer_id, product_id, store_id, order_date, status)

What would be the SQL statement to come up with:

• a list of stores in city X
• that carry the product Y, and
• list the stores by the total number of orders in asc order?

Joining store and products to get stores-carrying-products-in-city is like a text-book example of joining. Counting orders while grouping by store and ordering by count is a very common operation in report writing.

If you're doing homework, I gave you at least as much help as a TA would give you. If this is for work, then you should really think hard about your current situation :-) This query should take at most a minute for anyone who knows SQL to write and test, and I think you'd learn much more by working it out yourself based on these hints than just getting a canned answer, so I'm trying to be as helpful as I can be!

Finally, why did you tag this both "sql server" and "mysql" ? Those are two very different SQL implementations.

Try this.

SELECT Stores.name
  FROM Stores
       INNER JOIN StoresProducts
         ON Stores.id = StoresProduct.store_id
       INNER JOIN Products
         ON StoresProduct.product_id = Products.id
       INNER JOIN Orders
         ON Stores.id = Orders.id
 WHERE Stores.city = 'New York'
       AND Products.name = 'Shampoo'
 GROUP BY Stores.name
 ORDER BY COUNT(*); 

You just ask for the "total number of orders", not "total number of orders of product Y". If you need that, add an extra predicate to the join on Orders.

select
 s.name, s.city, count(o.id) as numoforders
from
 stores s join
 storesproducts sp on sp.store_id = s.id join
 products p on p.id = sp.product_id join
 order o on o.store_id = o.store_id
where
 s.city = @city and p.id = @productid
group by
 s.name, s.city
order by
 3 desc