Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Posts
    5

    Unanswered: Join two identically structured views

    Hi

    I have two views that contain the same columns of the same types. They also have the same amount of rows with the same primary keys.

    E.g.
    Code:
    VIEW_A
    ID	VALUE
    1	(null)
    2	1001
    3	(null)
    
    VIEW_B
    ID	VALUE
    1	81007941
    2	(null)
    3	(null)
    I would like to create a third view VIEW_COMBO that combines the two views, getting as many values as possible. I.e. getting the value 81007941 from VIEW_B and the value 1001 from VIEW_A and (null) for the third row where no value exists.

    E.g.
    Code:
    VIEW_COMBO
    ID	VALUE
    1	81007941
    2	1001
    3	(null)
    Is this possible somehow to write a sql query that does that?

    Best regards,
    Mattias

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look at UNION and UNION ALL in the documentation.

    Andy

  3. #3
    Join Date
    Sep 2008
    Posts
    5
    Thanks for your suggestion. I've checked the documentation on UNION ALL and tried the following:

    Code:
    CREATE VIEW VIEW_COMBO AS 
    SELECT * FROM VIEW_A 
    UNION ALL 
    SELECT * FROM VIEW_B
    WITH ROW MOVEMENT;
    The view is created and contains the following:
    Code:
    SELECT * FROM VIEW_COMBO;
    ID	VAL
    1	(null)
    2	2000
    3	(null)
    1	1000
    2	(null)
    3	(null)
    So now I get allt the rows in the new view, but this is not really what I wanted. I wanted it to end up like this:
    Code:
    ID	VAL
    1	1000
    2	2000
    3	(null)
    Is that possible?

    Best regards, Mattias

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You need to use UNION instead of UNION ALL if you want to eliminate duplicate rows.

    But I believe that you have semantics like this: For each ID value, use the VAL value that is not NULL. Right? What happens if the same ID value occurs in both tables and the VAL value is not NULL in both? Which one do you pick? If we can just take an arbitrary one (or the one from the first table), this would do - untested:
    Code:
    SELECT COALESCE(t1.id, t2.id), COALESCE(t1.val, t2.val)
    FROM   t1 LEFT OUTER JOIN t2 ON t1.id = t2.id
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2008
    Posts
    5
    Quote Originally Posted by stolze
    You need to use UNION instead of UNION ALL if you want to eliminate duplicate rows.

    But I believe that you have semantics like this: For each ID value, use the VAL value that is not NULL. Right? What happens if the same ID value occurs in both tables and the VAL value is not NULL in both? Which one do you pick? If we can just take an arbitrary one (or the one from the first table), this would do - untested:
    Code:
    SELECT COALESCE(t1.id, t2.id), COALESCE(t1.val, t2.val)
    FROM   t1 LEFT OUTER JOIN t2 ON t1.id = t2.id
    Yes! That's exactly what I want to do, and your suggestion works like a charm Problem solved! Thank you very much!

    Best regards,
    Mattias

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Btw, you may want to use a FULL OUTER JOIN instead of only a LEFT OUTER JOIN. Otherwise, you wouldn't get any ID values from the table on the right side of the join if they are only in that table.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2008
    Posts
    5
    Quote Originally Posted by stolze
    Btw, you may want to use a FULL OUTER JOIN instead of only a LEFT OUTER JOIN. Otherwise, you wouldn't get any ID values from the table on the right side of the join if they are only in that table.
    Thanks, the ID values will always exist in all rows in both left and right table though.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    In that case, use a regular INNER JOIN to avoid unnecessary additional processing in DB2.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Sep 2008
    Posts
    5
    Quote Originally Posted by stolze
    In that case, use a regular INNER JOIN to avoid unnecessary additional processing in DB2.
    Ok, I will. Thank you so much for all your help!

    Best regards,
    Mattias

Posting Permissions

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