在另一个基础上填充mysql表

Question

I have 3 mysql tables containing a few fields. Please refer to the following

--------  --------  --------
table 1 | table 2 | table 3
--------  --------  --------
a_id    | b_id    | email
                  | a_id
name    | status  | b_id
email_1 | date    | name
phone   | ref     | phone
address | email_2 | address
state   | from    | state
                  | status
                  | date
                  | ref
                  | from

The email_1 and email_2 is exactly same.

I need to populate the table3 fields with all table1 and table2 data. But I need to store them in one row based on email. So they look like following:

=================================================================================
                                   table 3
=================================================================================
email | a_id | b_id | name | phone | address | state | status | date | ref | from
------+------+------+------+-------+---------+-------+--------+------+-----+-----
[e-mail]|  9   | 112  | John |  999  | xxxx    |   NY  |    0   | 15Jue| dave|  y
------+------+------+------+-------+---------+-------+--------+------+-----+-----
[e-mail]|  6   | 338  | Sue  |  909  | xxxx    |   NY  |    1   | 12Jue| kell|  z
------+------+------+------+-------+---------+-------+--------+------+-----+-----
[e-mail]|  3   | 152  | John |  679  | xxxx    |   NY  |    1   | 10Jue| lois|  g
------+------+------+------+-------+---------+-------+--------+------+-----+-----
[e-mail]|  8   | 145  | John |  599  | xxxx    |   NY  |    0   | 8Jue | sue |  f

I can't figure it out how to do that. I'm using core php, mysql. Any help please?

Thanks!

Answer 1

I would use something like this for mysql:

insert into table_3
(
    `email`,
    `a_id`,
    `b_id`,
    `name`,
    `phone`,
    `address`,
    `state`,
    `status`,
    `date`,
    `ref`,
    `from`
)
select
    `a`.`email_1`,
    `a`.`a_id`,
    `b`.`b_id`,
    `a`.`name`,
    `a`.`phone`,
    `a`.`address`,
    `a`.`state`,
    `b`.`status`,
    `b`.`date`,
    `b`.`ref`,
    `b`.`from`
from 
    table_1 as `a`
    inner join
    table_2 as `b` on `a`.`email_1` = `b`.`email_2`

But you should probably go read up on MySQL insert syntax to see it's power, and on joining data :)