SELECT Problem

1. Senior Member
Join Date
Dec 1969
Posts
1,203

## SELECT Problem

Sorry for the vague title, but I couldn&#039;t think of a way to summarize my question in so short a space.<BR><BR>Database: Access 2000<BR><BR>Tables:<BR>Product_Price<BR> fields:<BR> product_id<BR> minimum_quantity<BR> price<BR><BR>Shopping_Cart_Products<BR> fields:<BR> product_id<BR> quantity<BR><BR>Each product may have different prices based on the quantity of product ordered. (Think quantity discounts).<BR><BR>I need to select the product price applied based on the quantity of items in the basket, but for all products, not just one. The problem is easy to solve when one wishes to find only the correct price to apply to a single product based on the quantity of that product in a person&#039;s basket, but more difficult when working with all of the products.<BR><BR>I would normally hack away at it a bit until I found the answer, but this is an urgent request from our CEO, so I figured I would post the question here, and will hack at it concurrently.

2. Senior Member
Join Date
Dec 1969
Posts
1,203

## RE: SELECT Problem

clarification:<BR><BR>Add a cart_id field to the Shopping_Cart_Products table.<BR><BR>My results should look like this:<BR><BR>cart_id product_id quantity price (quantity*applied price)<BR>0000001 0000000034 00000015 \$0225<BR>0000001 0000000153 00000058 \$1160<BR>0000002 0000002906 00000032 \$1560<BR><BR>Hope that&#039;s a little clearer. Thanks in advance for any help...

3. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Would this work?

What database are you using?<BR><BR>SELECT cart.product_id, cart.quantity, price.price<BR>FROM Shopping_Cart_Products cart<BR> INNER JOIN (SELECT TOP 1 price.price FROM Product_Price WHERE minimum_quantity &#060; cart.quantity ORDER BY minimum_quanity DESC)

4. Senior Member
Join Date
Dec 1969
Posts
3,195

## You can add a max_quantity field

to your product_prices table. Then each record in product_prices would be a range of quantities for that particular price. Just make sure you don&#039;t have any overlapping rangers for a product id, then you can do something like following:<BR><BR>Product_prices<BR>----------------- <BR>product_id <BR>min_quantity<BR>max_quantity <BR>price <BR><BR>Shopping_Cart<BR>---------------<BR>cart_id<BR>product_id <BR>quantity <BR><BR>SELECT a.cart_id, <BR> a.product_id, <BR> a.quantity, <BR> a.quantity * (SELECT b.price<BR> FROM product_prices b<BR> WHERE a.product_id = b.product_id<BR> AND (a.quantity &#062;= b.min_quantity<BR> AND a.quantity &#060;=b.max_quantity) )<BR> FROM shopping_cart a<BR><BR>You can expand these tables anyway you like. For example, you can add effective dates for your product price ranges. That way you can put in prices in advance(i.e. - for weekend sale maybe)and also will allow you to see the past history for your pricing.<BR><BR>You get the idea. <BR><BR>Good luck<BR>Pete<BR>

5. Senior Member
Join Date
Dec 1969
Posts
1,203

## RE: Would this work?

That would probably work. I will try it out. For the purposes of the urgent project, I got my boss to agree to take the numbers without taking the quantity discounts into account.<BR><BR>I&#039;ll have to produce more accurate numbers soon, though, so I will give your query a run...<BR><BR>Thanks...

6. Senior Member
Join Date
Dec 1969
Posts
1,203

## RE: You can add a max_quantity field

Actually, I can&#039;t. We designed the database with the max_quantity field, but the client asked us to remove it, as he was afraid that it would make DB maintenance too difficult. So, while your solution makes the most sense, my client won&#039;t accept it.<BR><BR>With one hour of notice, I was asked to produce some sales figures from the customer&#039;s online shop. The CEO is meeting the customer and wants to discuss the site&#039;s performance.<BR><BR>Talk about stress - the CEO will present my hastily generated numbers to the client - if they&#039;re way off, the CEO looks like an idiot and I get my *** chewed.<BR><BR>Anyway, thanks for the suggestion - only wish the client would accept it...

7. Senior Member
Join Date
Dec 1969
Posts
3,195

## Sounds like a

"fun" client to work with ;)<BR><BR>Seriously though, I think you are really in a tough spot. I understand your position, however in my experience (and probably yours), as the client&#039;s business and knowledge grows they are probably going to ask for these things in the future. Anything you can do to make things more flexible in your design up front (even if you don&#039;t necessarily use them right now) will only make your life easier.<BR><BR>Anyways, I just don&#039;t see an easy or perfect solution for those requirements with your present table structure. <BR><BR>Good luck<BR>Pete<BR><BR>P.S. If your CEO knew about this meeting beforehand and what they wanted, then maybe he deserves to look like an idiot ; )

8. Senior Member
Join Date
Dec 1969
Posts
3,195

## Try modifying God's query a little

that sounds so funny ; )<BR><BR>SELECT a.cart_id, <BR> a.product_id, <BR> a.quantity, <BR> a.quantity * (SELECT TOP 1 b.price <BR> FROM product_prices b <BR> WHERE a.product_id = b.product_id <BR> AND a.quantity &#060; b.min_quantity <BR> ORDER BY b.minimum_quanity DESC) <BR>FROM shopping_cart_products a <BR><BR>Still not a great solution for future.<BR><BR>good luck<BR><BR>Pete<BR>

9. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Why a sub-query ...

.. like you have instead of what I&#039;ve shown?<BR><BR>I think it makes more sense to do it my way, because then you can (easily) refer to the PRICE column instead of the whole SELECT statement.<BR><BR>And, why do you alias your tables as A, B, (presumably C, D, E..)? Do you name your ASP variables X, Y, Z, J, I, K? Why not give them descriptive names? It&#039;ll make maintenance MUCH easier.

10. Senior Member
Join Date
Dec 1969
Posts
3,195

## Just another way...

Often times there are many ways to do something ; )<BR><BR>Actually I liked your idea thats why I used it in my reply.<BR><BR>However since you brought this up, where were you actually joining on product id? Did I miss something?<BR><BR>Again I use A, B, C out of habit and its actually a pretty commonly used way to alias tables. Personally, when working with many tables it allows me to easily keep track of the ordering of my tables and I find it quicker to write my SQL.<BR><BR>Your way of using the first letter of table is fine too and commonly used.<BR><BR>Lastly, I like to name my variables var1,var2, var3, etc ; )<BR><BR>Pete

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•