SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: SQL query

  1. #1
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL query

    I have 2 tables - orders and shipments.

    orders:
    order_id
    estimated_shipping_price

    shipments:
    shipment_id
    order_id
    actual_shipping_price

    I want a query that will return the order_id and shipping price for that order. Some orders have only an estimated_shipping_price, some have acutal_shipping_price and some have both. The shipping price should be calculated as follows:

    if estimated_shipping_price > 0 then
    shipping price = estimated_shipping_price
    else
    shipping price = actual price

    Can it be done in a single query?
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  2. #2
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Milton, Ontario, Canada
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes you can do that. This solution may be more complex than required, and someone may have a better way... but:

    Code:
    declare @est_shipping_price int
     
    SELECT @est_shipping_price = estimated_shipping_price
    FROM orders 
    WHERE order_id = 1
     
    If @est_shipping_price > 0
     Begin
      SELECT estimated_shipping_price 'shipping_price'
      FROM orders
      WHERE order_id = 1
     End
    Else
     Begin
      SELECT actual_shipping_price 'shipping_price'
      FROM shipments
      WHERE order_id = 1
     End

  3. #3
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This'll work on MS SQL Server and most other DB's.

    Code:
    SELECT	CASE
    		WHEN O.estimated_shipping_price > 0 THEN O.estimated_shipping_price
    		ELSE S.actual_shipping_price
    	END AS 'Shipping Price'
    FROM	Orders O
    	INNER JOIN Shipments S ON O.order_id = S.order_id

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would modify shane's to use an outer join

    just for those cases where there is an order without a matching shipping record

    (that never happens? oh, okay, then the outer join will return the exact same results as the inner -- but if it does happen, then the inner join will miss those orders)
    Code:
    select case when O.estimated_shipping_price > 0 
                  or S.actual_shipping_price is null
                then O.estimated_shipping_price
                else S.actual_shipping_price
            end as ShippingPrice
      from Orders O
    left outer
      join Shipments S 
        on O.order_id = S.order_id
    rudy
    http://r937.com/

  5. #5
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Beautiful. Thanks for your help. I am finding that outer joins are almost a must when joining several tables with possible nulls, otherwise a single null will keep relevent data from being returned.
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.


Bookmarks

Posting Permissions

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