joining 2 tables and display column

[yxez]

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

[abhaysk]

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

[hrishy]

Hi

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)

[abhaysk]

Hi

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...)

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

[hrishy]

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

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

[yxez]

Wow!... i am impressed lots of brilliant solutions I got...thanks a lot to all
of you...and GOD BLESS

[yxez]

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

[hrishy]

Hi

You need to tell us that if that is result you want after looking at the results from the query.

regards
Hrishy

[yxez]

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

[hrishy]

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)

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

  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