full outer join

[ang90]

Hello,

Can someone show an example of full outer join on multiple table joins .. If I am joining 5 different tables using 4 different fields ... In my situation, I have no idea how much data is missing in various tables and so I am considering using full outer joins in all join clauses.

Just to give you a quick idea (not the real exaple):

SALARY


100 D1 HUNDRED
200 D2 TWOHUNDRED
300 D1 THREEHUNDRED
400 D4 FOURHUNDRED

EMPLOYEE

100 JOHN
200 MIKE
300 JEFF

ADDRESS

C1 MIAMI
C2 LA
C3 NY

DEPARTMENT

D1 ACCOUNTING
D2 PAYROLL
D3 HR
D4 FINANCE


Thanks

 

[amrita418]

You will need to List the join conditions for all tables.
Sample Code:

Select  Salary.EmpID, 
        Salary.DeptID,
        Employee.EmpID,
        Address.Join_Column,
        Department.DeptID
From    Salary
        Full Outer Join Employee
        ON (Salary.EmpID = Employee.EmpID)
        Full Outer Join Address
        ON (CorrespondingTable.Join_column = Address.Join_Column)
        Full Outer Join Department 
        ON (Salary.DeptID = Department.DeptID)

It would be a good idea to break down the query to 2-3 tables at a time to better understand what you are missing.

Regards,
AA

 

[ang90]

Hi,

Thank you ...

My query involves 5 tables with multiple joins .. I am not retrieving data from 4 tables using join condition on a column from first table.

Please take a look at the below code. Can you suggest how I can use full outer join here on all join conditions


select PRO.EID empnum, MAS.LY LY, MAS.PP PP, ACTY.DNO DNO
ACTY.CODE CODE, TRN.TC_ID TNO, AT.day day
from MAS, PRO, AY, ACTY, TRN, AT
WHERE MAS.TA_ID = PRO.TA_ID
AND MAS.TA_ID = TRN.TA_ID
AND TRN.ACCT_ID = AY.ACCT_ID
AND TRN.ACCT_ID = AT.ACCT_ID
AND AY.ACTY_ID = ACTY.ACTY_ID
AND MAS.PP = 22
AND MAS.LY = 2000

 

[Wholsea]

Which table is your driving table, that you know will have at least one record for each entity you are looking for?

That would make it easier to figure how your joins should be set up...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise