DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: union

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    union

    Good day,

    I have two tables with the same structure

    table A & B
    -------------
    id (int)
    parent_id (int)
    active (1 in table A / 0 in table B)

    I want to union the two tables but i want the end result to override certain values so if table A and table B have the same id the entry from table B won't be in the final table. Maybe its not a union at all, thats why i'm asking for help :-)

    example
    Table A Table B
    ---------- -------------
    1 null 1 54 45 0
    2 1 1 60 65 0
    3 2 1 10 3 0
    10 3 1


    the result would be

    Table (A u B)
    ---------------
    1 null 1
    2 1 1
    3 2 1
    10 3 1
    54 45 0
    60 65 0

    Scott

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Something like:
    Code:
    Select  . . . from A
    UNION ALL
    Select  . . . from B
    Where not exists
    (select * from A
     where  A.id = B.id)

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Just to show another way of doing it ...
    Code:
    Select
       id,
       parent_id,
       active,
       source
    From
       (
       Select
          source,
          id,
          parent_id,
          active,
          Min(source) Over
             (Partition By id) min_id_source
       From
          (
          Select
             'A' source,
             id,
             parent_id,
             active
          From
             a
          Union All
          Select
             'B',
             id,
             parent_id,
             active
          From
             b
          )
       )
    Where
       source = min_id_source
    /
    Not suggesting that it's any better mind (I doubt that it would be), but it would be interesting to see a performance comparison of the two methods. * hint hint *
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 2003
    Posts
    90

    union

    thanks for the quick response guys...

    DaPi, i was thinking of trying it your way but how come i can't do the following

    select *
    from
    (select id from caw_communities) a
    union ALL
    (select id from caw_tabs) b
    ;

    I get SQL command not properly ended.

    i was trying to name the tables for the where not exists a.id = b.id

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    re slimdave's method:
    1) yes, it could be faster
    2) it's clear evidence of sadly warped mind (and in one so young)
    3) It's going to cost a fortune to maintain (slimedave is expensive!)

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by DaPi
    ... slimedave ...
    You and Mr. Hanky both have problems with your e's today.

    Or maybe it was deliberate ....
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by DaPi
    re slimdave's method:
    1) yes, it could be faster
    2) it's clear evidence of sadly warped mind (and in one so young)
    3) It's going to cost a fortune to maintain (slimdave is expensive!)
    PS: excessive e's removed - sorry Dave

    PPS: Oh bother! didn't mean to quote myself - wrong button.

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by luciffer
    select *
    from
    (select id from caw_communities) a
    union ALL
    (select id from caw_tabs) b
    ;

    I get SQL command not properly ended.

    i was trying to name the tables for the where not exists a.id = b.id
    Your select can be corrected by adding brackets, e.g.:
    Code:
    select *
    from 
    (
    	 (select id from caw_communities) a
    union ALL 
    	 (select id from caw_tabs) b
    )
    ;
    but I don't see what that achieves.

    If you look at the UNION part of slimdaves's query, you will see how you could label where each row comes from . . . but if you're going down that road, slimdave has given a good solution already.

    Were you trying for a FULL OUTER JOIN? I think it could be done that way, something like:
    Code:
    select 
    nvl(a_id, b_id), 
    decode(a_id, NULL, b_col, a_col)
    from(
     select a.id a_id, a.col a_col, b.id b_id, b.col b_col
     from   a, b 
     where  a.id=b.id(+)
       UNION
     select a.id a_id, a.col a_col, b.id b_id, b.col b_col
     from   a, b 
     where  a.id(+)=b.id
    )
    and it might compete with slimdave's for speed?

  9. #9
    Join Date
    Dec 2003
    Posts
    90

    union

    select *
    from
    (
    (select id from caw_communities) a
    union ALL
    (select id from caw_tabs) b
    )
    ;
    this gives the error missing right parenthesis...

    I'm trying to do this to use your code you posted
    Select . . . from A
    UNION ALL
    Select . . . from B
    Where not exists
    (select * from A
    where A.id = B.id)

    table A is going to be a combination of tables and so is table B and that iswhy i'm trying to name them, so that when i do the

    where no exists i can use A.id and B.id

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Sorry about the brackets I guess it's Oracle's way of saying that it makes no sense.

    I don't have time to build a realistic example - but if you can imagine the following to use four different tables it should give you a start (this time I checked it! - 8.1.7) you would probably need to replace the "select *" with column names:
    Code:
    select * from
    rl_admin_flags a, rl_configuration b
    where a.rlparameter = b.rlparameter
       UNION ALL
    select * from
    rl_admin_flags c, rl_configuration d
    where c.rlparameter = d.rlparameter
    and   not exists
    (select * from rl_configuration x
     where x.rlparameter = d.rlparameter)
    Alternativly create two views to "encapsulate" your combination of tables.

Posting Permissions

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


Click Here to Expand Forum to Full Width