Help with 5 Table Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with 5 Table Query

Author

Topic

sqldude
Starting Member

2 Posts

Posted - 2008-09-21 : 23:52:42

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 >> 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.

I am familiar with JOINS and basic SQL but am a little rusty so any help/advice on how to get the results the way I want them would be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 03:27:10

you need to retrieve data flattened out from tables like

customer >> order 1 >> Product 1 >> Manufacturer
customer >> order 1 >> Product 2 >> Manufacturer
customer >> order 1 >> .. >> ..
customer >> order 2 >> Product 1 >> Manufacturer
customer >> order 2 >> Product 2 >> Manufacturer
i think above can be got by means of simple join between tables on related fields.

the formatting you require need to done at the report application. which report application are you using?

sqldude
Starting Member

2 Posts

Posted - 2008-09-24 : 18:06:59

I'm using PHP with a MySQL backend. How would I change the SELECT statement in my original post to flatten the tables out as you suggested? Thank you for replying.

quote:
Originally posted by visakh16

you need to retrieve data flattened out from tables like

customer >> order 1 >> Product 1 >> Manufacturer
customer >> order 1 >> Product 2 >> Manufacturer
customer >> order 1 >> .. >> ..
customer >> order 2 >> Product 1 >> Manufacturer
customer >> order 2 >> Product 2 >> Manufacturer
i think above can be got by means of simple join between tables on related fields.

the formatting you require need to done at the report application. which report application are you using?

sky2aone
Starting Member

5 Posts

Posted - 2008-09-24 : 21:54:50

group by order and product too

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 01:06:46

quote:
Originally posted by sqldude

I'm using PHP with a MySQL backend. How would I change the SELECT statement in my original post to flatten the tables out as you suggested? Thank you for replying.

quote:
Originally posted by visakh16

you need to retrieve data flattened out from tables like

customer >> order 1 >> Product 1 >> Manufacturer
customer >> order 1 >> Product 2 >> Manufacturer
customer >> order 1 >> .. >> ..
customer >> order 2 >> Product 1 >> Manufacturer
customer >> order 2 >> Product 2 >> Manufacturer
i think above can be got by means of simple join between tables on related fields.

the formatting you require need to done at the report application. which report application are you using?

just use simple join between the various tables and retrieve the relevant data from each.

SELECT o.order_id,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
ORDER BY manufacturers_name ASC , c.customers_lastname ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources