hmmh hmmh - 5 months ago 10
SQL Question

How to join table with selective duplicate records? (oracle10g)

How can I create an ORACLE query for the following requirement?

The question is If same ID is existed in some of SQL results, how can I get the record of that ID which Name is not blank or NULL.


Pattern(1)
SQL1 => RESULT1 => ID NAME
----- ------
001
002
003

SQL2 => RESULT2 => ID NAME
----- ------
003 NAME1

SQL3 => RESULT3 => ID NAME
----- ------
003 NAME2


Pattern(2)
SQL1 => RESULT1 => ID NAME
----- ------
001
002
003 NAME1

SQL2 => RESULT2 => ID NAME
----- ------
003

SQL3 => RESULT3 => ID NAME
----- ------
003 NAME2



Pattern(3)
SQL1 => RESULT1 => ID NAME
----- ------
001
002
003

SQL2 => RESULT2 => ID NAME
----- ------
003 NAME1
003 NAME2

SQL3 => RESULT3 => ID NAME
----- ------


How can I join these 3 results to become the following result?


ID NAME
----- ------
001
002
003 NAME1
003 NAME2

Answer

This working for your scenario

with tbl(id,name) as
(query1 
 union all
query2
 union all
query 3
)
select * From tbl where name is null
 and id not in
    (select id from tbl where name is not null)
union all
select * from tbl where name is not null

I have created sample example

with tbl(id,name) as
(select 1,null from dual union all
select 2,null from dual union all
select 3,null from dual union all
select 3,'NAME1' from dual union all
select 3,'NAME2' from dual)

select * From tbl where name is null
 and id not in
    (select id from tbl where name is not null)
union all
select * from tbl where name is not null

Output

id  name
---------
1   
2   
3   NAME1
3   NAME2