Mohammad Alsolaiman
asked on
How to union three related tables in one flat file?
Hi;
I have three tables for holding salary master data.
1- tblMaster
2- tblAllowance
3- tblDeduction
tblMaster has columns of:
o mstId
o mstEmpNo
o mstHireDate
o mstBasicSlary
o …etc.
tblAllowance has columns of:
o mstEmpNo as foreign key to link with tblMaster table.
o mstAllowanceCode
o mstAllowanceAmount
tblDeduction has columns of:
o mstEmpNo as foreign key to link with tblMaster table.
o mstDeductionCode
o mstDeductionAmount
for each employee several allowances & several deductions.
The allowances for one employee range from 5 until 10 allowances.
The deductions for one employee range from 2 until 6 allowances.
I need to have a flat file, contains only one record for each employee.
tblFlatSalary has columns of:
o fltId
o fltEmpNo
o fltHireDate
o fltBasicSlary
o fltAllowanceCode01
o fltAllowanceAmount01
o fltAllowanceCode02
o fltAllowanceAmount02
o fltAllowanceCode03
o fltAllowanceAmount03
o fltAllowanceCode04
o fltAllowanceAmount04
o fltAllowanceCode05
o fltAllowanceAmount05
o fltAllowanceCode06
o fltAllowanceAmount06
o fltAllowanceCode07
o fltAllowanceAmount07
o fltAllowanceCode08
o fltAllowanceAmount08
o fltAllowanceCode09
o fltAllowanceAmount09
o fltAllowanceCode10
o fltAllowanceAmount10
o fltDeductionCode01
o fltDeductionAmount01
o fltDeductionCode02
o fltDeductionAmount02
o fltDeductionCode03
o fltDeductionAmount03
o fltDeductionCode04
o fltDeductionAmount04
o fltDeductionCode05
o fltDeductionAmount05
o fltDeductionCode06
o fltDeductionAmount06
please help me!
I have three tables for holding salary master data.
1- tblMaster
2- tblAllowance
3- tblDeduction
tblMaster has columns of:
o mstId
o mstEmpNo
o mstHireDate
o mstBasicSlary
o …etc.
tblAllowance has columns of:
o mstEmpNo as foreign key to link with tblMaster table.
o mstAllowanceCode
o mstAllowanceAmount
tblDeduction has columns of:
o mstEmpNo as foreign key to link with tblMaster table.
o mstDeductionCode
o mstDeductionAmount
for each employee several allowances & several deductions.
The allowances for one employee range from 5 until 10 allowances.
The deductions for one employee range from 2 until 6 allowances.
I need to have a flat file, contains only one record for each employee.
tblFlatSalary has columns of:
o fltId
o fltEmpNo
o fltHireDate
o fltBasicSlary
o fltAllowanceCode01
o fltAllowanceAmount01
o fltAllowanceCode02
o fltAllowanceAmount02
o fltAllowanceCode03
o fltAllowanceAmount03
o fltAllowanceCode04
o fltAllowanceAmount04
o fltAllowanceCode05
o fltAllowanceAmount05
o fltAllowanceCode06
o fltAllowanceAmount06
o fltAllowanceCode07
o fltAllowanceAmount07
o fltAllowanceCode08
o fltAllowanceAmount08
o fltAllowanceCode09
o fltAllowanceAmount09
o fltAllowanceCode10
o fltAllowanceAmount10
o fltDeductionCode01
o fltDeductionAmount01
o fltDeductionCode02
o fltDeductionAmount02
o fltDeductionCode03
o fltDeductionAmount03
o fltDeductionCode04
o fltDeductionAmount04
o fltDeductionCode05
o fltDeductionAmount05
o fltDeductionCode06
o fltDeductionAmount06
please help me!
It would be best to use pivoting to convert your Allowance and Deductions tables rows into columns and join them with your master table. Create pivoted views of the 2 child tables and join them with tblMaster. Here's some sample code for pivoting. The below code will convert rows of tblAllowance to columns. Code hasn't been tested though.
select mstEmpNo, mstAllowanceCode01,mstAllowanceAmount01, mstAllowanceCode02, mstAllowanceAmount02
from (
select distinct mstEmpNo,
mstAllowanceCode,
mstAllowanceAmount
from tblAllowance
)
PIVOT (
MAX(mstEmpNo) AS mstEmpNo, MAX(mstAllowanceCode) AS mstAllowanceCode, MAX(mstAllowanceAmount) AS mstAllowanceAmount
FOR mstAllowanceCode in ('value of allowancecode1', 'value of allowancecode2'..... 'value of allowancecode10')
)
ORDER BY mstEmpNo;
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can try something like this. Add column aliases as you want.
select *
from tblMaster m
join (
select *
from tblAllowance
pivot (max(alwAllowanceAmount)
for alwAllowanceCode in ([101],[102],[103])) -- add your alwAllowanceCodes here;
) a on m.mstEmpNo = a.mstEmpNo
join (
select *
from tblDeduction
pivot (max(dedDeductionAmount)
for dedDeductionCode in ([201],[202],[203])) -- add your dedDeductionCodes here;
) d on m.mstEmpNo = d.mstEmpNo;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I try Steve Wales code , and I fund it OK. I need to try the other participates .
thank to all of u
thank to all of u
ASKER
Thanks to all of you who participated. It was so helpful.
<Total air code, you'll have to modify this to meet your needs>
Open in new window
Couple of questions: