merge some columns from some tables into one new table

Question

I need to create new table and, after that, copy some columns of some table into the new table. Three columns from the first table. Five columns from the second table. Three columns from the third table. Three columns from the fourth table.

The columns from table one: eid(primary key),first name,last name,status.
The columns from table two: mid(primary key), sender, subject,body,folder.
The columns from table three: rid(primary key) , mid ,rvalue.
The columns from table four: rfid(primary key), mid, reference.

How can I do this?

Columns are not that important. What rows would You insert into the new table? — Roman Hocke
– Roman Hocke, Commented Jun 13, 2017 at 9:36
each column hava 149 rows..i want to copy all of them raws into new table. — hsi
– hsi, Commented Jun 13, 2017 at 9:38
Then simply use INSERT INTO newtable(...) SELECT whatever from the old tables. How do You recognize corresponding rows in the "old" tables? — Roman Hocke
– Roman Hocke, Commented Jun 13, 2017 at 9:39
i have foreign key and primary key..but i do not know what is the exact syntax of the code for this work. — hsi
– hsi, Commented Jun 13, 2017 at 9:42
"i have foreign key and primary key..but i do not know what...". Probably you must facilitate your work with some mysql-gui like MySqlFront or FlySpeed Sql Query. They have a useful query builders that can cross your needs. — Nineoclick
– Nineoclick, Commented Jun 13, 2017 at 9:46

ScaisEdge · Accepted Answer · 2017-06-13 09:40:58Z

1

If the tables are related between them you could use directly a create table as select ..

    create table your_table as 
    select 
        a.col1
        , a.col2
        , a.col3
        , b.col1
        , b.col2
        , b.col3
        , b.col5
        , c.col1
        , c.col2
        , c.col3
        , d.col1
        , d.col2
        , d.col3
    from table1 a
    inner join table2 b on a.key1 = b.key1
    inner join table3 c on a.key1 = c.key1
    inner join table4 d on a.key1 = d.key1

this create the resulting table direcly from select

otherwise you can use an insert select

answered Jun 13, 2017 at 9:40

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nineoclick · Accepted Answer · 2017-06-13 09:40:53Z

INSERT INTO new_table (field_1,field_2...field_n)
SELECT first_table.column_1
    ,first_table.column_2
    ,first_table.column_3
    ,second_table.column_1
    ,second_table.column_2
    ,second_table.column_3
    ,second_table.column_4
    ,second_table.column_5
    ,third_table.column_1
    ,third_table.column_2
    ,third_table.column_3
    ,fourth_table.column_1
    ,fourth_table.column_2
    ,fourth_table.column_3
FROM first_table
INNER JOIN second_table ON first_table.field_to_join = second_table.field_to_join
INNER JOIN third_table ON second_table.field_to_join = third_table.field_to_join
INNER JOIN fourth_table ON third_table.field_to_join = fourth_table.field_to_join

Clearly, you have to tune your query, your joins/type of joins and fields according to your needs.

In (field_1,field_2...field_n) you have to indicate fields where the output of the select query will go.

Collectives™ on Stack Overflow

merge some columns from some tables into one new table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related