Forum Moderators: open
I have a database intranet app i'm doing in dreamweamweaver mx. This question involves the logic between three tables.
When a customer places an order with the app it creates an order reference number. The table contains customer name, Sales Order number, and customer's Purchase order, if they gave one.
Now we need to add a product to the order. I have a different table of products with part number, description, category of product, and price. The category comes from a product_type table, so that new categories can be added.
I then have a little joining table called orderdetails with an incrementing primary key called detailref, the orderref from the order table, and thepartno from the products table.
So say for instance I add stock code B1, a banana, 20p, from the Fruit n Veg category, onto Order 192, some of the rows in the tables would look like this.
ORDER TABLE
SalesOrder 192 Customer 56 PurchaseOrder 12345
ORDERDETAILS TABLE
detailref 555 SalesOrder 192 PartNo B1
PRODUCTS TABLE
PartNo B1 Description A Banana Price 20p
We need to display info from the SalesOrder Table and the relevant products on order from the Products table on a Sales Order Page in Dreamwever. We link onto the page from another page with a list of sales orders. We can display the ORDER TABLE details on the page no sweat. Now we must display each of the products that have been added to the order.
*********************************************************
The task is to display the description and price for all items purchased on that particular sales order only.
<Proposed Solution?>
select the partno for each line in the orderdetails table where the salesorder is 192
select the product description and price from the products table for that part number
</Proposed Solution?>
The solution may or may not be either through some groovy MySQL statement, or knowing what to do in dreamweaver. Remember though that you may order the same item twice, it would appear as another line in the order details table. Good Luck Brainsters!
Your better off persisting the product prices at the time an order is received; as your stuffing them into the order details table.
The thing about prices is that they change over time and updates to your Products Table will cause inaccurate reports against your sales summary tables based on your initial system description.
SELECT o.SalesOrder, detailref, d.PartNo, Description, Price
FROM [ORDER TABLE] AS o
JOIN ORDERDETAILS AS d
ON o.SalesOrder = d.SalesOrder
JOIN PRODUCTS AS p
ON d.PartNo = p.PartNo
Here's a sequence secenario
item entered into products table at 99.00
item sells for 99.00
administrator adjusts price of this item to 89.00
All summary reports for this item are now going to pull 89.00 for this sale when in fact it sold at 99.00
That's why I suggest persisting the item price at the time of sale in the order details table for each item in an order. That gives you price paid versus current offer price.
