-
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
-
Something like:
Code:
Select . . . from A
UNION ALL
Select . . . from B
Where not exists
(select * from A
where A.id = B.id)
-
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 *
-
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
-
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!)
-
Originally posted by DaPi
... slimedave ...
You and Mr. Hanky both have problems with your e's today.
Or maybe it was deliberate ....
-
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.
-
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?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|