Display each customer’s complete order. This will show a
separate line for each item in each order per customer. Sort by
customer_id, order_id, and item_id.

Display customer_id, customer name, customer order_id, sales_order.
order_date, sales_order.ship_date, item_id, product_id, product
description, item actual_price, item quantity, calculate line item
total

table customer (customer_id, name, address, city, state ,zip_code, area_code, phone_number, salesperson_id, credit_limit, comment)

table item (order_id, item_id, product_id, actual_price, quantity, total)

table product (product_id, description)

table sales_order ( order_id, order_date, customer_id, ship_date, total)

I'm confused as to what to write for the WHERE statement.
thanks, your help will be greatly appreciated!!!

you may not even need a WHERE if you're showing every record and connecting the tables with some JOINs.

I'm not sure what 'calculate line item total' means as there's already a total in the item. Is the total the 'total per unit' and you have to do it based on quantity maybe?

I think you want

select c.customer_id, c.name, so.order_id, so.order_date, so.ship_date, i.item_id, i.product_id, p.description, i.actual_price, i.quantity, i.total
from Customer c, sales_order so, item i, product p
where c.customer_id = so.customer_id
and so.order_id = i.order_id
and i.product_id = p.product_id
order by c.customer_id, so.order_id, i.item_id

you can also redo with joins and no 'where' statements.

The 'line item total' thing is unclear though, so clarification may render the above incorrect.

You need to start by joining the two main tables.
You have to display details for the customer so customer is the first table.

So what other table has customer_id in it? Only sales_order, so this means teh first join is between sales_order and customer


I alias my queries , so
select c.customer_id
from customer c,
sales_order so
where so.customer_id = c.customer_id

(Now you can any column from either of the two tables above that are required).

So whats next? I think we need to add in the items that have been ordered
so we have an order_id and there is an equivalent column in the sales_order table.

select c.customer_id
from customer c,
sales_order so,
item i
where so.customer_id = c.customer_id
and i.order_id = so.order_id

So now the only other thing we need is to display the product_description.
so we must add in the product table. Where else is the product_id. The Item table.

select c.customer_id
from customer c,
sales_order so,
item i,
product p
where so.customer_id = c.customer_id
and i.order_id = so.order_id
and p.product_id = i.product_id.

So now we have the basis of our query. We have joined all our tables.

we have 2 things left to do:
i) Add in the columns that we wish to display (from the appropriate table using the appropriate alias)
ii) put an order by statement at the end.


And no matter how hard or complex the query gets, these principles always apply.

Couldn't resist:



Seems better suited for SO.