SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    . gdape's Avatar
    Join Date
    Jan 2002
    Location
    in the outskirts, and in the fringes
    Posts
    501
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    combine two rows data where some empty fields (get those from parent)

    I want to retrieve data from one row and where the fields are empty get the data from another row (with the same product_id). So here I want to get the data from row 3 but for all the fields that are empty get those from row 1:

    product_id, v_id, name, summary, price
    1, "1", "beans", "delicious and tasty", "10.00"
    1, "2", "", "", "11.00"
    1, "3", "fava beans", "", "12.00"
    1, "4", "", "", "13.00"

    select * from product where product_id='1' and v_id='3' (then if field='' get from v_id='1')

    I hope that makes sense, thank you in advance.

  2. #2
    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)
    Code:
    SELECT v3.product_id
         , COALESCE(v3.name,v1.name) AS name
         , COALESCE(v3.summary,v1.summary) AS summary
         , COALESCE(v3.price,v1.price) AS price
      FROM product AS v3
    LEFT OUTER
      JOIN product AS v1
        ON v1.product_id = v3.product_id
       AND v1.v_id = 1        -- note no quotes
     WHERE v3.product_id = 1  -- note no quotes
       AND v3.v_id = 3        -- note no quotes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •