By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Return all items in an order then add the shipping cost to only one item

P: 29
So I am having a difficult time with some clients. The database is set up like this
Expand|Select|Wrap|Line Numbers
  1. tblOrder               tblOrder_Item_Bridge              tblItem
  2. internal_id            item_id                             item_id
  3. ship_cost             internal_id                         item desc
  4. item_id                                                    cost  
  5.                                                            quantity
Pretty simple

Now the clients want something like this

Expand|Select|Wrap|Line Numbers
  1. internal_id  item desc        quantity   cost           ship_cost        
  2. 1643089    EGRET             3          7.75         $13.15     
  3. 1643089    EGSPSAE           4           3.5        $0.00 
the problem is they want the first line to inluce the shipping cost and every line after that, the shipping cost is zero. Andy ideas on how to do this? here is the query that generates a report of every ordered item with the orders ship cost:

Expand|Select|Wrap|Line Numbers
  1. SELECT     tblOrder.internal_id, tblOrder.order_number,  tblItem.quantity, tblOrder.ship_cost, 
  2. FROM       tblOrder INNER JOIN
  3.            items ON tblOrder.internal_id = tblOrder_Item_Bridge .order_Id INNER JOIN
  4.            item ON tblOrder_Item_Bridge.items_Id = tblItem.items_Id
  5.  
  6.  
That generates this:

Expand|Select|Wrap|Line Numbers
  1. internal_id  item desc        quantity   cost           ship_cost        
  2. 1643089    EGRET             3          7.75         $13.15     
  3. 1643089    EGSPSAE           4           3.5        $13.15
Nov 10 '09 #1
Share this Question
Share on Google+
2 Replies


Delerna
Expert 100+
P: 1,134
What are you using as a front end?
You may be able to handle the issue there.
Check the internal_ID and display zero instead of the shipping cost for every record other than the first.

Just wondering.
What happens if the price of an item changes?
It appears to me that all orders that were at the orriginal price will suddenly change to the new price.
Not good if you want to report historical info.
Nov 10 '09 #2

P: 1
TRY
Expand|Select|Wrap|Line Numbers
  1. SELECT     tblOrder.internal_id, tblOrder.order_number,  tblItem.quantity, CASE WHEN tblItem.items_Id=1 THEN tblOrder.ship_cost ELSE 0 END as shipCost, 
  2. FROM       tblOrder INNER JOIN
  3.            items ON tblOrder.internal_id = tblOrder_Item_Bridge .order_Id INNER JOIN
  4.            item ON tblOrder_Item_Bridge.items_Id = tblItem.items_Id
  5. ORDER BY CASE WHEN tblItem.items_Id=1 THEN tblOrder.ship_cost ELSE 0 END DESC
Nov 11 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.