CODEWITHSUNDEEP
sqloracle-database
VJay
VJay

Reputation: 49

Join two tables without nulls reflecting in the results

Below are the 2 tables that I try to join,

table 1:

+--------+--------+---------+------+-----+--------+
| seq_id | req_id | ctrl_id | CODE | c2  | status |
+--------+--------+---------+------+-----+--------+
|      1 |      3 | C001    | ABC  |   0 | PASS   |
|      2 |      3 | C001    | EFG  |   0 | PASS   |
|      3 |      3 | C001    | HIJ  |   0 | PASS   |
+--------+--------+---------+------+-----+--------+

table 2:

+--------+--------+---------+--------+-------+-------------+
| seq_id | Req_id | ctrl_id | source |  c1   |     c2      |
+--------+--------+---------+--------+-------+-------------+
|      1 |      5 | C001    | S1     |   ABC | 32331235662 |
|      2 |      5 | C001    | S1     |   EFG |  8966492700 |
|      3 |      5 | C001    | S1     |   HIJ | 12656678121 |
|      4 |      5 | C001    | S2     |   ABC | 32331235662 |
|      5 |      5 | C001    | S2     |   EFG |  8966492700 |
|      6 |      5 | C001    | S2     |   HIJ | 12656678121 |
+--------+--------+---------+--------+-------+-------------+

I'm using the below query to join these tables,

select a.seq_id, a.req_id, a.ctrl_id, a.CODE,
       (case when b.source = 'S1' then  b.c2 end) as source1 ,   
       (case when b.source = 'S2' then  b.c2 end) as source2, a.c2, a.status
from table1 a  
  join  table2 b on  a.ctrl_id=b.ctrl_id  and a.code=b.c1
order by a.seq_id

the result that i get is,

seq_id  Req_id  ctrl_id CODE    SOURCE1          SOURCE2        c2  status
1          5    C001    ABC      NULL             32331235662   0   PASS
1          5    C001    ABC      32331235662      NULL          0   PASS
2          5    C001    EFG      NULL             8966492700    0   PASS
2          5    C001    EFG      8966492700       NULL          0   PASS
3          5    C001    HIJ      NULL             12656678121   0   PASS
3          5    C001    HIJ      12656678121      NULL          0   PASS

I am trying to get the below as result, without the null values,

seq_id  Req_id  ctrl_id CODE    SOURCE1          SOURCE2        c2  status
1          5    C001    ABC      32331235662      32331235662   0   PASS
2          5    C001    EFG      8966492700       8966492700    0   PASS
3          5    C001    HIJ      12656678121      12656678121   0   PASS

Please assist.

Upvotes: 0

Views: 64

Answers (2)

arahman
arahman

Reputation: 585

You could use joins to get the two separate fields you are after and just amend them onto your original dataset.

In this case the joins are SELF JOINS to the same table to get the specified values.

    select a.seq_id, b.req_id, a.ctrl_id, a.CODE,
           s1.c2 AS [Source1] ,
           s2.c2 AS [Source2],
           a.c2,
           a.status
    from table1 a  
      LEFT OUTER JOIN table2 b 
            ON a.seq_id = b.seq_id
      LEFT OUTER JOIN table2 s1
            ON s1.req_id = b.req_id AND s1.c1 = b.c1 AND s1.[source] = 'S1'
      LEFT OUTER JOIN table2 s2
            ON s2.req_id = b.req_id AND s2.c1 = b.c1 AND s2.[source] = 'S2'
    order by a.seq_id

Upvotes: 0

Viktor Bardakov
Viktor Bardakov

Reputation: 876

Just use grouping for this task

Like this

select a.seq_id, a.req_id, a.ctrl_id, a.CODE,
       max(case when b.source = 'S1' then  b.c2 end) as source1 ,   
       max(case when b.source = 'S2' then  b.c2 end) as source2, a.c2, a.status
from table1 a  
  join  table2 b on  a.ctrl_id=b.ctrl_id  and a.code=b.c1
group by a.seq_id, a.req_id, a.ctrl_id, a.CODE,a.c2, a.status
order by a.seq_id

Upvotes: 3

Related Questions