Hi,
I am having trouble figuring out the best way to structure a query that is pulling data from 5 tables. I need to display a report in PHP that shows something like the following:
customer 1 >> order 1 >> Product 1 >> Manufacturer
>> Product 2 >> Manufacturer
>> .. >> ..
>> order 2 >> Product 1 >> Manufacturer
>> Product 2 >> Manufacturer
customer 2 >> order 1 >> Product 1 >> Manufacturer
>> Product 2 >> Manufacturer
>> .. >> ..
>> order 2 >> Product 1 >> Manufacturer
>> Product 2 >> Manufacturer
etc.
I need to display the orders and all products within the orders for each customer. Below is the query that I have that only displays the first order and first product for each customer.
SELECT op.products_id, manufacturers_name, c.customers_lastname, op.products_name, op.products_quantity
FROM orders AS o, orders_products AS op, manufacturers AS m, products AS p, products_to_categories AS pc, customers AS c
WHERE o.orders_id = op.orders_id
AND op.products_id = p.products_id
AND p.manufacturers_id = m.manufacturers_id
AND o.customers_id = c.customers_id
GROUP BY c.customers_lastname
ORDER BY manufacturers_name ASC , c.customers_lastname ASC
This results in the following:
customer 1 >> order 1 >> product 1 >> manufacturer
customer 2 >> order 1 >> product 1 >> mfr
customer 3 >> order 1 >> product 1 >> ...
etc.
Can anyone tell what I'm doing wrong so that I only get the first order and product within that order for each customer?
I am a little rusty so any help/advice on how to get the results the way I want them would be greatly appreciated.