Derived table instead of a subquery

from the Artful MySQL Tips List

Example of using a derived table

If you don't have those tables, but do have a version of the Microsoft NorthWind example database, here is a NorthWind version of that query:

select c.companyname, c.customerid, o.orderid, o.orderdate, sum(d.quantity)
from customers c, orders o , orderdetails d  
where o.orderid in (
    select orderid
    from orderdetails
    group by orderid
    having sum(quantity) > 10
  and c.customerid = o.customerid
  and o.orderid = d.orderid
group by c.companyname, c.customerid, o.orderid, o.orderdate
order by o.orderdate
LIMIT 100     ;

Last updated 16 Aug 2019

Return to the Artful MySQL Tips page