1.11M Members

Problem with Sql Query using join on 2 different tables

 
0
 

Hi, I am currently working on joining 2 tables, however those tables have different number of columns thats why I have difficulty on joining them. I tried to use FULL OUTER JOIN, close result but still not my expected output.
You can kindly see my attachment to see the scenario.

Oracle Sql Query:

SELECT a.id, b.firstName, b.lastName, b.midInitial, a.project, a.manager FROM table1 a FULL OUTER JOIN table2 b WHERE a.id = b.id

By the way, I'd used this query to that expected scenario and still not getting same on that screenshot. Hoping someone who can guide me to this. Thanks.

8aa4f26a7c434114a57cc057d85df164

Attachments
 
1
 
SELECT CASE WHEN NVL(a.id, '') = '' THEN b.id ELSE a.id as ID
        , NVL(b.firstName, '-') as [First Name]
        , NVL(b.lastName, '-') as [Last Name]
        , NVL(b.midInitial, '') as [Middle Iniital] 
        , NVL(a.project, '-') as [Current Project]
        , NVL(a.manager, '-') as [Assigned Manager]
FROM table1 a 
FULL OUTER JOIN table2 b on a.id = b.id 
 
0
 

Thanks sandeep, I'll try this asap. Never thought of using NVL. Thanks again.

 
0
 

Finally I made it working and through corrections I made from sandeepmittal11 (many thanks to him)

Here is my final sql that matches on my attached screenshot.

SELECT 
    CASE 
    WHEN nvl(t2.userid,'') IS NULL 
    THEN t1.userid else t2.userid
    END user_id,

nvl(t2.first_name,'-') as First_name,
nvl(t2.last_name,'-')as Last_name,
nvl(t2.middle_initial,'-') as Middle_Initial,
nvl(t1.current_project,'-') as Current_Project,
nvl(t1.manager,'-') as Project_Manager

FROM table1 t1

FULL OUTER JOIN table2 t2 ON t1.userid = t2.userid

ORDER BY t1.userid
You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: