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
Post a Comment