Hi,


I have tables that i need to insert all data from that table into one table.

here is tables that I need to get the data.

1. totalpay AS t

EMP_NO, Amount, TotalEarnings, TotalDeductions, TakeHomePay

2. other_earnings AS e

EMP_NO, OTReg_Amt, SunReg_Amt, OTSun_Amt, HolReg_Amt, HolRegOT_Amt, HolLeave_Amt, NP_Amt, Meal_Amt, Cola_Amt

3. deductions AS d

EMP_NO,SSS, TAX, PCHL, HDMF

4. hdmfloan AS h
EMP_NO, HDMFAmor

5. sssloan AS s

EMP_NO, SSSAmor

6. udtloan AS u
EMP_NO, UDTAmor

7. other_deductions AS o
EMP_NO, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther


And here is the table where all data should be inserted

1. generate_payroll

EMP_NO, Basic_Pay, OTReg_Amt, SunReg_Amt, OTSun_Amt, HolReg_Amt, HolRegOT_Amt, HolLeave_Amt, NP_Amt, Meal_Amt, Cola_Amt, TotalEarnings, SSS, TAX, PCHL, HDMF, HDMF_Amor, SSS_Amor, UDT_Amor, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, TotalDeductions, TakeHomePay


I used this code:

Code:
$result = mysql_query("INSERT INTO generate_payroll (EMP_NO, Basic_Pay, OTReg_Amt, SunReg_Amt, OTSun_Amt, HolReg_Amt, HolRegOT_Amt, HolLeave_Amt, NP_Amt, Meal_Amt, Cola_Amt, TotalEarnings, SSS, TAX, PCHL, HDMF, HDMF_Amor, SSS_Amor, UDT_Amor, BurialSeparationCont, TaxAjt, CashAdvance, AdvanceShirt, AdvanceMed, AdvanceOther, TotalDeductions, TakeHomePay) SELECT t.EMP_NO, t.Amount, e.OTReg_Amt, e.SunReg_Amt, e.OTSun_Amt, e.HolReg_Amt, e.HolRegOT_Amt, e.HolLeave_Amt, e.NP_Amt, e.Meal_Amt, e.COLA_Amt, t.TotalEarnings, d.SSS, d.TAX, d.PCHL, d.HDMF, h.HDMFAmor, s.SSSAmor, u.UDTAmor, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther, t.TotalDeductions, t.TakeHomePay FROM totalpay t, other_earnings e, deductions d, hdmfloan h, sssloan s, udtloan u, other_deductions o WHERE t.EMP_NO = e.EMP_NO AND d.EMP_NO = h.EMP_NO AND s.EMP_NO = u.EMP_NO = o.EMP_NO ON DUPLICATE KEY UPDATE EMP_NO = t.EMP_NO, Basic_Pay = t.Amount, OTReg_Amt = e.OTReg_Amt, SunReg_Amt = e.SunReg_Amt, OTSun_Amt = e.OTSun_Amt, HolReg_Amt = e.HolReg_Amt, HolRegOT_Amt = e.HolRegOT_Amt, HolLeave_Amt = e.HolLeave_Amt, NP_Amt = e.NP_Amt, Meal_Amt = e.Meal_Amt, Cola_Amt = e.Cola_Amt, TotalEarnings = t.TotalEarnings, SSS = d.SSS, TAX = d.TAX, PCHL = d.PCHL, HDMF = d.HDMF, HDMF_Amor = h.HDMFAmor, SSS_Amor = s.SSSAmor, UDT_Amor = u.UDTAmor, BurialSeparationCont = o.BurialSeparationCont, TaxAjt = o.TaxAjt, CashAdvance = o.CashAdvance, AdvanceShirt = o.AdvanceShirt, AdvanceMed = o.AdvanceMed, AdvanceOther = o.AdvanceOther, TotalDeductions = t.TotalDeductions, TakeHomePay = t.TakeHomePay")  or die(mysql_error());

for inserting and updating data, It dit not inserted data, and I think the problem is came from the where clause, honestly, right now i don't have any idea how can I check that all EMP_NO from the seven tables is equal.As you can see i check the EMP_NO in where clause, when I tried that in my where clause i only have this:

Code:
WHERE t.EMP_NO = e.EMP_NO
all data was inserted but is was duplicated a lot..


i hope someone could help me, while I'm waiting for help, i will search also for the solution..

Thank you