Link to home
Start Free TrialLog in
Avatar of Mohammad Alsolaiman
Mohammad AlsolaimanFlag for Saudi Arabia

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!
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

UNION is to add sets that have the same exact schema, which is not your requirements.  You'll want a JOIN.

<Total air code, you'll have to modify this to meet your needs>
SELECT m.this, a.that, d.theotherthing, m.yougettheidea
FROM tblMaster m
   JOIN tblAllowance a ON m.mstEmpNo = a.mstEmpNo
   JOIN tblDeduction d ON m.mstEmpNo = a.mstEmpNo

Open in new window

Couple of questions:
  • Are there more than one a and d rows for a given m?  If yes then you're going to have to figure out what gets added (SUM) and what gets counted (COUNT), as that will change the query.
  • Do you want m rows to display even if there are no related a and d rows?  If yes change JOIN to LEFT JOIN.
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;

Open in new window

Avatar of Mohammad Alsolaiman

ASKER

I'm afraid that I couldn't describe my need well.
Maybe this pictures can illustrate my need.
User generated imagei need to come up with one record only for each employee, like this :
User generated image
ASKER CERTIFIED SOLUTION
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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;

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I try Steve Wales code , and I fund it OK. I need to try the other participates .
 thank to all of u
Thanks to all of you who participated. It was so helpful.