jones77622 Posted November 18, 2013 Share Posted November 18, 2013 (edited) I have a database of 5 tables. 1st table is my customer table. 2nd is my crust table. 3rd is my toppings table. 4th is my sides table. 5th is my order table. My crust, toppings and sides table all have a price column and I am trying to get them to add together on my order table total cost column. What function would be the best to use to accomplish getting the crust price toppings price and sides price to add up on the order table Total cost column? Here is my table schema: Crust TableCrustID Primary KeyCrustnameCrustsizeCrustprice moneyToppings TableToppingID Primary KeyToppingNameToppingPrice moneySides TableSideID Primary KeySideNameSidePortionSidePrice moneyCustomer TableCustomerID Primary KeyFnameLnameStreetCityStateZipPhonenumberemailaddress Order TableOrderId Primary Key INTCustomer Foreign Key INTCrust Foreign Key INTToppings Foreign Key INT Sides foreign key int TotalCost money Edited November 18, 2013 by jones77622 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 18, 2013 Share Posted November 18, 2013 Can a single order have multiple toppings and sides? It looks like the order table has one column for toppings, and no column for sides. Link to comment Share on other sites More sharing options...
jones77622 Posted November 18, 2013 Author Share Posted November 18, 2013 (edited) Sorry about that i thought i put all of the order table in. I am starting with only one topping and one side but was thinking about adding more after i figure out how to properly add up cost. Edited November 18, 2013 by jones77622 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 18, 2013 Share Posted November 18, 2013 Well, the technique is going to be different with multiple toppings and sizes, the database structure for that would be different. Ideally you would store those using a third lookup table to link orders with toppings, and orders with sides. That would be a many-to-many relationship instead of the one-to-many relationship you have now. With the current structure you'll need to join the tables and then add up the appropriate columns. Something like this: SELECT o.OrderID, c.Crustname, t.ToppingName, s.SideName, c.Crustprice + t.ToppingPrice + s.SidePrice as OrderPrice FROM order AS o LEFT JOIN crusts AS c ON o.crust=c.crustID LEFT JOIN toppings AS t ON o.toppings=t.toppingID LEFT JOIN sides AS s ON o.sides=s.sideID 1 Link to comment Share on other sites More sharing options...
jones77622 Posted November 19, 2013 Author Share Posted November 19, 2013 (edited) How do i get it to show up on the order table when i view it in select top 1000 rows on the order table ??? It shows the proper price in the query but it does not show it on the ordertable query select top 1000 rows This is the query that worked so far SELECT OrderID, Crust, Toppings, Sides, Crustprice + ToppingPrice + SidePrice as OrderPrice FROM order1 AS o LEFT JOIN Crusttable AS c ON crust=crustID LEFT JOIN ToppingsTable AS t ON Toppings=toppingID LEFT JOIN sidestable AS s ON sides=sideID Edited November 19, 2013 by jones77622 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 19, 2013 Share Posted November 19, 2013 If you're talking about the TotalCost field on the order table, then the correct way to do that would be to calculate the total cost at the time you create the order and set the value for that field. You could use an update query to update all of the total cost fields for the whole table, but it wouldn't be a good design for the application if you need to update that table periodically in order to reflect the correct total cost for all orders. The total cost should be calculated when the order is created and you should add the record to the order table with the total cost value already filled in. Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 any ideas??? Link to comment Share on other sites More sharing options...
justsomeguy Posted November 20, 2013 Share Posted November 20, 2013 Any ideas for what? How do you add orders to that table now? Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 through the edit top 200 rows Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 i am trying to get it to calculate total cost automatically on the order table as i am entering the product in the order table. Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 one of my friends said i should try a multistatement table-valued functions but am not sure how to go about that. Link to comment Share on other sites More sharing options...
justsomeguy Posted November 20, 2013 Share Posted November 20, 2013 If you are editing the database manually then your options are limited. Most of the time databases are used they are interacted with by another programming language, where you would use that language to calculate the cost before you add the order record to the table. If you're adding records manually then one option would be to create a trigger for each time you add a new record to run the queries necessary to figure out the total cost for that record. Which database system are you using? Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 SQL management studio i believe it is SQL server 2008 r2 Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 (edited) i have been looking for a few weeks on how to do this i am working on this for a project for my SQL class as extra credit to boost my grade. Edited November 20, 2013 by jones77622 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 20, 2013 Share Posted November 20, 2013 This is the section on how to create triggers: http://technet.microsoft.com/en-us/library/ms189799.aspx You'll probably want to research triggers in general. It sounds like you need to create an insert trigger and an update trigger for the orders table, to run each time a record gets added or updated to calculate the cost. Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 what about using a multistatement table-valued function ?? Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 what would the correct syntax be for an insert into statement?? Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 using the same idea Link to comment Share on other sites More sharing options...
justsomeguy Posted November 20, 2013 Share Posted November 20, 2013 A function like that isn't going to update the data already in the table. If you want to make sure the table always contains accurate values for the cost then use a trigger. You can also use a view, but again a view is not going to change the data in the table. There are several examples of insert statements here: http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 well i have a table that doesn't have anything in it yet then i have a table that i am using for testing purposes Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 can i use the same idea in creating a function?? Link to comment Share on other sites More sharing options...
justsomeguy Posted November 20, 2013 Share Posted November 20, 2013 I don't know what you're asking. Which idea? You can use any idea to create any function, I suppose, as long as you design and implement it correctly. I don't know why you would want to use a function for this instead of a trigger, though. When do you execute the function? Link to comment Share on other sites More sharing options...
jones77622 Posted November 20, 2013 Author Share Posted November 20, 2013 i would like to execute this function every time i enter a new row in the order table. my guess is that i need to create a function then create a trigger to activate that function every time i enter a new row into the order table. am i correct? Link to comment Share on other sites More sharing options...
justsomeguy Posted November 20, 2013 Share Posted November 20, 2013 That's one way to do it. The trigger could also contain all of the code you need, as far as I know. Link to comment Share on other sites More sharing options...
jones77622 Posted November 21, 2013 Author Share Posted November 21, 2013 i am having trouble making a trigger for the order table can you help me with the syntax please? Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now