CODEWITHSUNDEEP
mysql
hsi
hsi

Reputation: 97

merge some columns from some tables into one new table

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?

Upvotes: 1

Views: 70

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

Nineoclick
Nineoclick

Reputation: 814

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.

Upvotes: 1

Related Questions