mysql 单独join,单个查询中的MySQL JOIN和COUNT

I'm trying to join 2 tables together and get the count of foreign keys...

I'm sorry, but I don't really how to explain myself, so let me demonstrate:

I have 1 table, 'orders', for orders, with the following fields:

id, f_name, l_name, credit_card, ETC.

Then, I have an 'orders_details' table for the items in the order, like so:

id, order_id, product_id, qty

Now, I want to run a query joining the 2 tables, getting 1 row per each row in the orders table, with a column telling me how many products are in each order.

Anybody know how to achieve this?

P.S. I'd also like to be able to get the total of all the 'qty' for the orders (I don't want to run a separate query for each order).

解决方案SELECT o.id, o.f_name, o.l_name, COUNT(od.id), COALESCE(SUM(od.qty), 0)

FROM orders o

LEFT JOIN order_details od ON o.id = od.order_id

GROUP BY o.id, o.f_name, o.l_name