Insert data into one table where data came from multiple tables

Databases
1

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

  1. other_earnings AS e

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

  1. deductions AS d

EMP_NO,SSS, TAX, PCHL, HDMF

  1. hdmfloan AS h
    EMP_NO, HDMFAmor

  2. sssloan AS s

EMP_NO, SSSAmor

  1. udtloan AS u
    EMP_NO, UDTAmor

  2. 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:


$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:


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

2

It dit not inserted data

What error did you get?

3

I don’t receive any error, just when I check in my database no data was saved…

Thank you…

i think the problem is in where clause…

Thank you

4

i found some query in this url: http://www.w3schools.com/sql/sql_select_into.asp

the problem is the sample is only for two tables.

Thank you

5

You might want to put your queries on multiple lines, it makes them easier to read. At least when you post them here, it makes them easier to read for us :wink:

Echo out the query, and copy and paste it in phpMyAdmin to see if it works.

6 

WHERE t.EMP_NO = e.EMP_NO AND d.EMP_NO = h.EMP_NO [COLOR="#FF0000"][B]AND s.EMP_NO = u.EMP_NO = o.EMP_NO[/B][/COLOR]

Does the part in red work? Could be, I don’t know.
All tables are linked to each other on EMP_NO?
Try


WHERE t.EMP_NO = e.EMP_NO 
AND   t.EMP_NO = d.EMP_NO 
AND   t.EMP_NO = h.EMP_NO 
AND   t.EMP_NO = s.EMP_NO
AND   t.EMP_NO = u.EMP_NO
AND   t.EMP_NO = o.EMP_NO
7

here is the code:


<?php
  $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());
?>

when i run this code in phpmyadmin,
the output is 0 row(s) inserted. ( Query took 0.0030 sec )

Thank you

8

You are missing some join criteria in your query, so you should change the WHERE clause (for example like I did above).
But, adding those missing criteria makes it only more probable to have 0 rows that satisfy the criteria.

Are you sure there are emp_no that are present in all seven tables?

9

It works when I change my where clause but only 2 rows was inserted instead of six…

i will attach my sql

Thank you

10

i think because i have only 2 datas in HDMF_Amor, SSS_Amor and UDT_Amor.

Thank you…

11

Yes

12

it works in terms of Insert but when i run again the query, it was duplicated…insted of save the updated files.

here is my new code:


<?php
  $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, l.HDMFLoan, l.SSSLoan, l.UDTLoan, o.BurialSeparationCont, o.TaxAjt, o.CashAdvance, o.AdvanceShirt, o.AdvanceMed, o.AdvanceOther, t.TotalDeductions, t.TakeHomePay
  FROM totalpay t, other_earnings e, deductions d, loan_deductions l, other_deductions o
  WHERE t.EMP_NO = e.EMP_NO AND t.EMP_NO = d.EMP_NO AND t.EMP_NO = l.EMP_NO AND t.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 = l.HDMFLoan, SSS_Amor = l.SSSLoan, UDT_Amor = l.UDTLoan, 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());
?>

Thank you

13

What is the duplicate you don’t want? EMP_NO? Then add a unique index on EMP_NO for the generate_payroll table.
Otherwise the ON DUPLICATE won’t work, because there are no duplicates.

14

What do you mean I need to add unique index? like what? and how?

Yes I want to base on the EMP_NO because that is the unique data.

Thank you

15

Thank you…

I add Unique in EMP_NO and now it did not duplicated…

Thank you so much…

16

Very good :slight_smile:
You’re welcome.