November 8, 2017 at 5:35 pm
Hello. I have two tables with a one to many relationship. The Master Table is named Stores and the child table is named Items purchased. I have a column named date_purchased in my Stores Table. I was wondering if I wanted to get the total dollar amount for Items purchased within a date range would I do an inner join on the two tables or do I need to also have a date_purchased column in my Items purchased table. Thanks for any help or suggestions.
November 8, 2017 at 11:19 pm
Having a "date purchased" column in Stores doesn't make any sense, unless you were recording the date the store was purchased. Normally, if a column doesn't describe the "subject" of the table, it doesn't belong there. Purchases have purchase dates, stores don't.
November 10, 2017 at 1:17 pm
Thanks for your comment. I thought it gave me some additional insight into design which is what I was asking for. It sounds like you are saying that I will will have a purchased_date column for every purchased_item in my table Items_Purchased. So that leads me to think that logically I will not need to add the Stores table in my query and just query the Items_Purchased Table for items purchased within a particular date range.
November 10, 2017 at 1:27 pm
Well normally for purchases you have two tables. OrderHead and OrderLine (or whatever you call them). You would record things like the customer, tax, shipping, orderDate etc in the OrderHead. The orderLine table would record things like UPC, Price, Quantity etc. From what you are have posted it seems that your biggest challenge is that the design of your tables is less than ideal for the data you are storing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply