Is JOIN with SUM() possible?

[dreystone]

I am having difficulty coming up with a valid syntax, and I don't even know if it is possible.

I have two tables: orders and order_item

orders has information like customer_id, date_purchased and so on.

order_item has:

id (key)
orders_id
product_id
quantity
unit_price
total_price

What I need to do is to get all (*) fields from orders, and (SUM(order_item.quantity) WHERE orders.id = order_item.orders_id) all in a single sql command.

Basically the syntax I am currently trying to get to work is:

SELECT a.*, SUM(b.quantity) FROM orders AS a, order_item AS b WHERE a.company_id = 3 AND a.id = b.orders_id

I have a feeling that I need to do a LEFT JOIN as a nested statement... Any ideas?

[hemebond]

SELECT a.*, SUM(b.quantity) FROM orders AS a LEFT JOIN order_item AS b ON a.id = b.orders_id WHERE a.company_id = 3

[dreystone]

Thanks for the assistance but running that query I get the following error:

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

[hemebond]

That has nothing to do with the JOIN, it's because you haven't got a GROUP clause for your SUM to work with.