Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2011-03-03
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


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 http://www.ballparkprints.com/detail.aspx?ID=24 is a sample of the page that needs to have the prices and sizes updated.
LVL 22

Assisted Solution

8080_Diver earned 400 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 1600 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

618 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