oracle - What SQL join do i need? -


i have 2 tables.

table1:

name date project hrs vkr  0727  x       8 vkr  0728        4 vkr  0728  b       4 vkr  0729  c       8 

table2:

name date project hrs vkr  0728  123      8 vkr  0729  234      8 vkr  0730  345      8 

i need join these tables on name , date , output expecting should below:

name date table1.project table1.hrs table2.project table2.hrs vkr  0727 x              8                     vkr  0728              4          123            8 vkr  0728 b              4 vkr  0729 c              8          234            8 vkr  0730                           345            8 

i have tried using full outer join doesn't seem work. query

select nvl(t1.name,t2.name), nvl(t1.date,t2.date), t1.project, t1.hrs,  t2.project, t2.hrs table1 t1  full outer join table2 t2 on t1.name = t2.name , t1.date = t2.date 

the issue face is, if have 2 rows same name , date in table1 , 1 row same key in table2, 2 rows table2, below:

 name date table1.project table1.hrs table2.project table2.hrs     vkr  0728              4          123            8     vkr  0728 b              4          123            8 

which not want.

any appreciated. in advance

the way data setup, there's no way vkr name on 0728 know if should include 123 project or not. need normalize data model cross reference table know projects related, or add column on table 2 store project in table 1 it's related to. table joins on name , date going give result set said don't want data setup way is.


Comments

Popular posts from this blog

javascript - Create a stacked percentage column -

Optimising Firebase database by automatically overwriting data -

javascript - Angular UI-Grid customTemplate directive causing rows to load slowly/? -