I need a SQL query that adds price and size data to a database

Posted on 2011-03-03
Last Modified: 2012-06-22
I need a query to add price and size data to a table. The name of the table is AttributeDetail.
Question by:philip38
  • 6
  • 4
  • 2
LVL 22

Expert Comment

ID: 35031915
I don't seem to have anything that will open the file types that you have attached.  Can you, perhaps, attach Word (or some other standard file type) equivalents?

Does your AttributeDetail table have columns for the price and size data?

I'm not at all sure how this is a difficult task . . . but, then again, I can't see the information you have provided. ;-)

Author Comment

ID: 35032212
Oh, Thank You.  Here attached are word files. At the office I only had the Open Office Draw/Suite programs.
LVL 22

Expert Comment

ID: 35032363
Okay, I can see the files now (which really helps ;-).

However, I don't exactly see how you plan to add the price and size information to that table . . . there don't appear to be any columns provided for some of the information.

I am assuming that the first set of valuies ("Size") is associated with an unframed image while the second is associated with framed images.  If that is true, then you are dealing with something like the following:
Image_Size	UnFramed	Framed
8.5x11	$60.00	NULL
11x14	$22.00	$ 95.00
16x20	$40.00	$140.00
18x24	$50.00	$180.00
20x28	$60.00	$225.00
26x36	$60.00	$295.00

Open in new window

Now, I am not at all sure how the colors come into play with the pricing.  And I am not at all sure how you are using the Attribute table.  Is the Attribute table used to indicate what attributes something is supposed to have?  And, then, are you somehow wanting to transfer the size/pricing information into the the AttributeDetail table?

Personally, I would tend to have a ImagePrices table with the Data, shown above, arranged in that manner (with a ImagePriceID column that is an Identity column as the primary key).  Then I would use the ImagePriceID to link from any Inventory/OrderDetail/whatever entries.

The use of the Attributes and AttributeDetail tables looks like a bit of over kill in the area of normalization to me.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Author Comment

ID: 35032930
Makes sense to me. I will have to run it by the guy who assigned this to me tomorrow. (Flooded with work). Really appreciate it. Please stand by.
LVL 50

Expert Comment

ID: 35034345
its difficult to understand what you are trying to do since you haven't given any background on the table relationships
or even the table structures...

i'd say that possibly your tables are wrongly named since it appears that attributes is really a productAttribute table
(similarly with attributedetail?).. are you sharing attributes between products ?  is there another table which details
the cross attribute relationships (e.g. you can't have a black frame above size X? or Pink frames are only in size Y)

are you updating existing rows with price info
or inserting new rows ?

do you have attributes already set up for FRAME ?

Author Comment

ID: 35038620
Thank you for your reply.
Let me enhance the explanation with another file. I have created a diagram of the key tables showing their relationships.

Also, I want to show you the end result page we are talking about. You can find it on the web here:

Author Comment

ID: 35038636
That is to say is a sample of the page that needs to have the prices and sizes updated.
LVL 22

Assisted Solution

8080_Diver earned 100 total points
ID: 35039222

Okay, in looking at your diagram, I am still trying to figure out where your size and cost figures go.  Are the separate entries in the AttributeDetail table or what?

I still think that the separate ImagePrices table would be the best bet.
Table Name: ImagePrices

Column Name            Datatype
ImagePriceID             INT (Identity)
ProductID                   INT   (FK to Products table)
ImageSizeID              INT   (FK to ImageSizes table)
ImageCost                 Currency

Table Name: ImageSizes
Column Name            Datatype
ImageSizeID               INT (Identity)
ImageSizeDesc         VarChar(50) (Description of ImageSize)

Open in new window


Author Comment

ID: 35039677
I think that is a viable suggestion, except I want to if possible keep working on the database as it is constructed.
Therefore, I have attached an enhanced bitmap of the database diagram. These include the tables that have the 'price' field.

My objective is to 1. change existing prices on the page and 2. add new size item entries to the page.
LVL 50

Accepted Solution

Lowfatspread earned 400 total points
ID: 35040282

i still think mostt of your problem is down to mis nameing of the tables, and i don't like how you've got "price" scattered about ...

but it is fairly simple to update your price

insert into attributedetails (list of table columns)
 values ( the values for each column)


insert into attributedetails (productid,attributeid,name,price,....)
 values (12,13,'11x8',49,00,...)

assuming that you have converted 'size' to its attributeid  e.g. 13
(framed is also an attribute in your system and it has names which are a subset of the set of names available to size(?))

but it starts to breakdown with colours... which (i think) is where our confusion comes in ...  you are missing a table which
controls the ability to mix the options...
 size, framed, color of frame, etc   ...

your order table has options for gift wrapping etc , i'm not sure why that is being treated differently from (say) framing...
you also have a saleitem indicator , but again is that at the correct place (could you not just have a sale on "pink" frames?)

 perhaps a better model for you to think of would be a pizza creation/order menu....

each component can be separately charged/changed/customised and the price is determined by having a Basic price (size)
to which the other product customisations then add to (e.g. sweetcorn,pepparoni,anchovies 0.50 each, Tuna,beefm,, 0.75)
then you get the options on crust, base type, how cut, delivery etc....


Author Closing Comment

ID: 35057564
Thank you both to Lowfatsrpead and to 8080 Diver
LVL 22

Expert Comment

ID: 35058652
I have to agree with Lowfatspread that having pricing information in 4 tables is a bit problematic.  I can see having it in the Product Table and in the OrderDetails table (because the price could change after the OrderDetails entry is created) but having it in the other tables looks a whole lot like the design needs more work.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Is there a better way to combine two queries 7 29
Database Integrity 1 51
Getting invalid Syntax SQL. 3 21
Use column to search string column 2 12
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question