What SQL join do i need?

Question

I have 2 tables.

Table1:

Name Date Project Hrs
VKR  0727  X       8
VKR  0728  A       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 to join these tables on Name and Date and the output I am expecting should look like below:

Name Date Table1.Project Table1.Hrs Table2.Project Table2.Hrs
VKR  0727 X              8                    
VKR  0728 A              4          123            8
VKR  0728 B              4
VKR  0729 C              8          234            8
VKR  0730                           345            8

I have tried using FULL OUTER JOIN but that doesn't seem to work. This is my query

Select nvl(T1.Name,T2.Name), nvl(T1.Date,T2.Date), T1.Project, T1.Hrs, 
T2.Project, T2.Hrs from Table1 T1 
full outer join Table2 T2 on T1.Name = T2.Name
and T1.Date = T2.Date

The issue I face is, If I have 2 rows for the same Name and Date in Table1 and 1 row for the same key in Table2, I get 2 rows from Table2, something like below:

 Name Date Table1.Project Table1.Hrs Table2.Project Table2.Hrs
    VKR  0728 A              4          123            8
    VKR  0728 B              4          123            8

which I do not want.

Any help is appreciated. Thanks in advance

You can start by reading the [`JOIN`](https://www.techonthenet.com/oracle/joins.php) plsql documentation to comprehend it? If you really don't know how to work with joins, try to comprehend it because joining tables happens in lots of database queries... — KarelG, Jul 28 '17 at 15:00
@KarelG - Did you read the question fully.. Even after using `FULL OUTER JOIN` all the records are not returned,why it works like `Inner join` thats the question. — Pரதீப், Jul 28 '17 at 15:06
Is this the complete statement? If it is, I'm pretty sure you won't get the result you posted. — Eric, Jul 28 '17 at 15:51

score 0 · Answer 1 · answered Jul 28 '17 at 14:59

The way your data is setup, there's no way for the VKR name on 0728 to know if it should include the 123 project or not. You would need to normalize your data model with a cross reference table to know which projects were related, or add a column on Table 2 to store which project in Table 1 it's related to. Any table joins on Name and Date are going to give you the result set you said you don't want with the data setup the way it currently is.

score 0 · Answer 2 · answered Jul 28 '17 at 15:10

As far as I understood the question , you want to display table2 fields only for the "first" row in table1 that satisfy join condition. A slightly modified version of your query will do the job :

with T1 as
(
   SELECT name, date, ROW_NUMBER() OVER(PARTITION BY name,date ORDER BY name) rn
   FROM Table1
)
Select nvl(T1.Name,T2.Name), nvl(T1.Date,T2.Date), T1.Project, T1.Hrs, 
CASE 
   WHEN T1.rn IS NULL OR T1.rn = 1 THEN T2.Project
END as t2_project, 
CASE 
  WHEN T1.rn IS NULL OR T1.rn = 1 THEN T2.Hrs 
END as t2_hrs

from 
T1 
full outer join Table2 T2 on T1.Name = T2.Name
and T1.Date = T2.Date

What SQL join do i need?

2 Answers2