user889349 user889349 - 6 months ago 10
SQL Question

MySQL | Request from 3 tables

I have a tables:

1. orders

id userid status placed
1 1 9 2014-05-02
2 1 8 2014-05-02

2. products

id qty name
1 1 Samsung galaxy s4
2 1 Samsung galaxy s4

3. orders_products

id orderid productid qty
1 1 1 2
2 2 2 2


My request looks like below:

SELECT P.name, SUM(OP.qty) count FROM `orders_products` OP, `products` P WHERE OP.productid = P.id GROUP BY P.id ORDER BY SUM(OP.qty) DESC LIMIT 10


How can I show results where order status = 9 for example and placed between two dates?

Thanks!

Answer

You can try this , its another variation of what you are trying to achieve.

select
p.id,
p.name,
coalesce (sum(`tot`),0) as `count`
from products p
inner join
(
  select 
  sum(qty) as `tot`,
  productid
  from orders_products
  inner join `orders` on `orders`.`id` = orders_products.orderid
  where `orders`.status = 9
  group by productid
)t
on p.id = t.productid
group by p.id
order by `count`

DEMO

Comments