hi all,
consider the following data
with data as
(
select 'HF' py, 123 cid, 'mike' nm from dual union all
select 'HF' py, 124 cid, 'will' nm from dual union all
select 'HF' py, 125 cid, 'smith' nm from dual
),
data2 as
(
select 'MP' py, 123 cid, 'mike-C' nm from dual union all
select 'MP' py, 124 cid, 'will-b' nm from dual union all
select 'MP' py, 126 cid, 'wayne' nm from dual
)
I want to join these two tables and get the following output
PY CID NM NM2
=========================
HF 123 Mike Mike
MP 123 mike-C Mike
HF 124 will will
MP 124 will-b will
HF 125 smith smith
MP 126 wayne wayne
basically i want to combine the data from both tables. if there is a match on CID, then NM2 should have the value of NM colum from the HF row in both HF and MP for the same CID
for example, cid=123 there is a match in data and data2 table. both rows should be display as is from their corresponding table. however, NM2 is a derive column and the value
should be the value from NM column from the HF row. in this case is Mike. so both HF and MP row with cid=123 will have nm2=Mike
same for cid=124, there is a match in both table. both rows display as is and nm2 should be 'will' which is the value from nm column from the HF row.
for cid=125, there is no match in the data2 table so row should be display as is and nm2 column value should come from nm column of the same HF row
for cid=126, there is no match in data table, so data should be display as is and nm2 column value should come from nm column of the same MP row
can somone help me write a query that produce the above output? i was doing union all between the two tables but then i got stuck on deriving the nm2 column
thanks in advance