Jump to content

osCommerce - 5 table query not pulling correct dataset


sqldude

Recommended Posts

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. 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.