Oracle: How to query separate records from multiple tables?

advertisements

I want to query multiple tables but only return unique records.

My current query:

select
    a.*,
    b.*,
    c.*
from c_inv a
    inner join p_master b
        on a.c_code = b.c_code
            and a.p_code = b.p_code
    left join c_proj c
        on b.c_code = c.c_code
            and b.p_code = c.p_code
where a.c_code = 'AA'
    and a.d_type = 'IN'
    and a.s_type = substr('OI',0,2)
    and a.g_flag = 'N'
    and a.startdate <= trunc(sysdate)
    and nvl(a.enddate, trunc(sysdate)) >= trunc(sysdate)
order by a.p_code

Data Sample

p_master
c_code  p_code
AA      Test01
AA      Test02
AA      Test03

c_proj
c_code  p_code      proj    startdate   enddate
AA      Test99      clound  01/10/2016  31/10/2016
AA      Test99      clound  01/09/2016  30/09/2016
AA      Test99      clound  01/08/2016  31/08/2016

my current results:

c_code  p_code
AA      Test01
AA      Test02
AA      Test03
AA      Test99
AA      Test99
AA      Test99

Target results:

c_code  p_code      proj
AA      Test01      null
AA      Test02      null
AA      Test03      null
AA      Test99      clound


Not sure how this output is USEFUL, but to get it, you need UNION, not a join. (Note - UNION also does a DISTINCT operation so you don't need to add it separately.)

select p_code, c_code, null as proj from p_master
union
select p_code, c_code,         proj from c_proj

The results may come in the order you showed, or in any other order; use explicit ORDER BY .... if you need a specific order.