-
joining 2 tables and display column
Hi Dearest Friends,
I have 2 tables
PAYMENT1 (id, amount1)
sample data:
100, 1000.00
101, 1000.00
102, 1000.00
PATMENT2 (id, amount2)
sample data:
100, 500.00
103, 500.00
I want to create a view joining the two table in such a way that it
will display the values of the other table even if it does not have
match on the other.
Payment_view (id, amoun1,amount2)
100, 1000.00, 500.00
101, 1000.00, null
102, 1000.00, null
103, null, 500.00
It this correct?
create view payment_view as select x.id,x.amount1,y.amount2
from payment1 x, payment2 y where x.id=y.id
union
select id,amount1,null from payment1 where id not in(select id from payment2)
union
select id,null,amount2 from payment2 where id not in(select id from payment1);
I find it awkward through Is there otherway to do it? They said using
"not in" degrades performance.
Thanks a lot
Last edited by yxez; 09-13-2007 at 06:00 AM.
Behind The Success And Failure Of A Man Is A Woman
-
Code:
create view payment_view as
select
x.id,
x.amount1,
y.amount2
from
payment1 x full outer join payment2 y on x.id=y.id
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi
Code:
SELECT
x.id,
x.amount1,
decode(y.amount2 ,NULL,'NULL',y.amount2)
FROM
payment1 x LEFT OUTER JOIN payment2 y
on (x.id=y.id)
-
 Originally Posted by hrishy
Hi
Code:
SELECT
x.id,
x.amount1,
decode(y.amount2 ,NULL,'NULL',y.amount2)
FROM
payment1 x LEFT OUTER JOIN payment2 y
on (x.id=y.id)
I think he even needs all the ids that are in payment2 even if payment1 dosent have any entries against it.. but the code you suggested would not give desired results..
check his code and get the gist what he wants to achieve.. (though he has not put in words properly.. he has expressed in code...)
Code:
create view payment_view as
select x.id,x.amount1,y.amount2
from payment1 x, payment2 y where x.id=y.id
union
select id,amount1,null
from payment1 where id not in (select id from payment2)
union
select id,null,amount2
from payment2 where id not in (select id from payment1)
;
rgds
abhay
Last edited by abhaysk; 09-13-2007 at 05:20 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi
A picture is worth a thousand words i was carried off by the nice result the o/p posted :-).
I guess what the o/p needs is
Code:
SELECT
x.id,
x.amount1,
decode(y.amount2 ,NULL,'NULL',y.amount2)
FROM
payment1 x FULL OUTER JOIN payment2 y
on (x.id=y.id)
regards
Hrishy
-
Wow!... i am impressed lots of brilliant solutions I got...thanks a lot to all
of you...and GOD BLESS
Behind The Success And Failure Of A Man Is A Woman
-
Hi Again Friends,
I got another problem
I have a 3rd table (NAME_MASTER z) and I want to join it to (PAYMEN1 x, PAYMENT2 y) with simple JOIN.
Is this the right way to do it?
SELECT
x.id,
x.amount1,
y.amount2,
z.fullname
FROM
payment1 x FULL OUTER JOIN payment2 y
on (x.id=y.id), NAME_MSTER z where z.id=x.id;
But I got multiple result cartesian product or 3 tablers :(
Thanks again
Last edited by yxez; 09-17-2007 at 10:08 PM.
Behind The Success And Failure Of A Man Is A Woman
-
Hi
You need to tell us that if that is result you want after looking at the results from the query.
regards
Hrishy
-
Thanks dear its like this for a clearer picture....
I have 3 tables
PAYMENT1 (id, amount1)
sample data:
100, 1000.00
101, 1000.00
102, 1000.00
PATMENT2 (id, amount2)
sample data:
100, 500.00
103, 500.00
NAME_MASTER (id, fullname) # 3rd table
100, hrishy
101, abhaysk
102, davey
103, pando
OUTPUT > Payment_view (id, amoun1,amount2,fullname)
100, 1000.00, 500.00, hrishy
101, 1000.00, null, abhaysk
102, 1000.00, null, davey
103, null, 500.00, pando
Behind The Success And Failure Of A Man Is A Woman
-
Hi
Well looking at the way you have shown me the results what you really need is this (loking at the way your tables are also named)
Code:
SELECT
x.id,
decode(y.amount1,NULL,'NULL',y.amount1) amount1,
decode(z.amount2 ,NULL,'NULL',z.amount2)amount2,
x.fullname
FROM
name_master x
LEFT OUTER JOIN payment1 y on (x.id=y.id)
LEFT OUTER JOIN payment2 z on (x.id=z.id)
order by x.id
and the result is
Code:
ID AMOUNT1 AMOUNT2 FULLNAME
---- ------------ ---------- --------------------
100 1000 500 hrishy
101 1000 NULL abhaysk
102 1000 NULL davey
103 NULL 500 pando
You dont need a full outer join just the names of the people who have id's in either payment1 or payment2 table no ?
A small request please post create table statements and insert statements so the person who is solving this problem does not have to do trivial things like creating those it does not aid to solving the core of the problem
regards
Hrishy
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
|