php join 3 tables then insert to 4th

Question

Here is the JOIN:

SELECT 
    gc3025_raid_results.raid_id, 
    gc3025_game_units.planet_id, 
    gc3025_game_units.g_dist, 
    gc3025_game_units.unit_name, 
    gc3025_units_base.unit_type, 
    gc3025_game_units.code, 
    gc3025_game_units.cdamage, 
    gc3025_units_base.defense_raid_score, 
    gc3025_units_base.raid_damage 
FROM gc3025_game_units 
    JOIN gc3025_units_base 
        ON gc3025_game_units.code = gc3025_units_base.unit_code 
    JOIN gc3025_raid_results 
        ON gc3025_raid_results.district_idg = gc3025_game_units.g_dist

I then need to insert into another table

INSERT INTO gc3025_raid_defenders( 
    raid_ids, planet_id, district_id, 
    unit_name, unit_type, unit_code, 
    raid_score, raid_damage)
    SELECT ... (see above) ...

Any suggestions would be appreciated.

maybe?: http://stackoverflow.com/questions/1382842/mysql-insert-joins — Martin Hrabal, Feb 01 '16 at 16:55
It worked but it also created over a million duplicates in the table. there should only be 4. — Jason B, Feb 01 '16 at 17:20

score 0 · Answer 1 · answered Feb 01 '16 at 17:04

0

This should get you going, but you will need to add the other fields. Make sure the name and order of the fields in the insert() match the fields in the select()

insert into gc3025_raid_defenders (raid_ids, planet_id, etc...)
select (gc3025_raid_results.raid_id, gc3025_game_units.planet_id, etc...)
from gc3025_game_units gu
join gc3025_units_base ub
    on gu.code = ub.unit_code
join gc3025_raid_results rr
    on gu.g_dist = rr. district_idg

On a side note, think about naming your fields like g_dist consistently between tables, if possible.

answered Feb 01 '16 at 17:04

Jeff

9,076
1
19
20

Thanks. I was doing that but then when I joined two tables sometimes those field names would screw things up. It almost worked. I got unknown column in field list for every column in the Select bracket. Then I added single quotes to them and it now says "Operand should contain 1 column. – Jason B Feb 01 '16 at 17:55
oops. I declared a table alias of gu, ub and rr , so your field name should be like rr.raid_id, gu.planet_id , etc... – Jeff Feb 01 '16 at 18:15

php join 3 tables then insert to 4th

1 Answers1