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

[cday119]

So I am having a difficult time with some clients. The database is set up like this

tblOrder               tblOrder_Item_Bridge              tblItem
internal_id            item_id                             item_id
ship_cost             internal_id                         item desc
item_id                                                    cost  
                                                           quantity

Pretty simple

Now the clients want something like this

internal_id  item desc		quantity   cost           ship_cost        
1643089	EGRET             3	      7.75	 	$13.15 	
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:

SELECT     tblOrder.internal_id, tblOrder.order_number,  tblItem.quantity, tblOrder.ship_cost, 
FROM       tblOrder INNER JOIN
           items ON tblOrder.internal_id = tblOrder_Item_Bridge .order_Id INNER JOIN
           item ON tblOrder_Item_Bridge.items_Id = tblItem.items_Id

That generates this:

internal_id  item desc		quantity   cost           ship_cost        
1643089	EGRET             3	      7.75	 	$13.15 	
1643089	EGSPSAE           4	       3.5		$13.15

[Delerna]

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.

[bbdd]

Use case

TRY

SELECT     tblOrder.internal_id, tblOrder.order_number,  tblItem.quantity, CASE WHEN tblItem.items_Id=1 THEN tblOrder.ship_cost ELSE 0 END as shipCost, 
FROM       tblOrder INNER JOIN
           items ON tblOrder.internal_id = tblOrder_Item_Bridge .order_Id INNER JOIN
           item ON tblOrder_Item_Bridge.items_Id = tblItem.items_Id
ORDER BY CASE WHEN tblItem.items_Id=1 THEN tblOrder.ship_cost ELSE 0 END DESC