sqldude Posted September 26, 2008 Share Posted September 26, 2008 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. Link to comment https://forums.phpfreaks.com/topic/125902-oscommerce-5-table-query-not-pulling-correct-dataset/ Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.