1,105,644 Community Members

Problem with Sql Query using join on 2 different tables

Member Avatar
batuzai04123
Newbie Poster
11 posts since Jan 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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
Member Avatar
sandeepmittal11
Newbie Poster
2 posts since Jun 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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 
Member Avatar
batuzai04123
Newbie Poster
11 posts since Jan 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
batuzai04123
Newbie Poster
11 posts since Jan 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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 three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: