database - mySQL - Create a New Table Using Data and Columns from Three Tables -

database - mySQL - Create a New Table Using Data and Columns from Three Tables -

i trying create new table consist of selected info 3 existing tables.

the 3 existing tables set following, , desired result @ bottom:

people id last_name first_name email 1 smith fred fred@.. 2 jones tom tom@.. 3 doe jane jane@.. taxonomy id taxonomy 1 age 2 gender 3 height details id person_id detail_id content 1 1 1 36 2 1 2 m 3 1 3 5'10" 4 2 1 29 5 2 2 m 6 2 3 6'3" 7 3 1 27 8 3 2 f 9 3 3 5'8" new table id last_name first_name email age 1 smith fred fred@.. 36 2 jones tom tom@.. 29 3 doe jane jane@.. 27

thanks in advance help!

you need 3-way join:

create table new_table select p.*, d.content age people p bring together details d on d.person_id = p.id bring together taxonomy t on t.id = d.detail_id t.taxonomy = 'age'

demo

or if you've created table, can do:

insert new_table (id, last_name, first_name, email, age) select p.id, p.last_name, p.first_name, p.email, d.content age people p bring together details d on d.person_id = p.id bring together taxonomy t on t.id = d.detail_id t.taxonomy = 'age'

to multiple attributes, have bring together details , taxonomy tables separately each attribute:

create table new_table select p.*, d1.content age, d2.content gender, d3.content height people p bring together details d1 on d1.person_id = p.id bring together taxonomy t1 on t1.id = d1.detail_id bring together details d2 on d2.person_id = p.id bring together taxonomy t2 on t2.id = d2.detail_id bring together details d3 on d3.person_id = p.id bring together taxonomy t3 on t3.id = d3.detail_id t1.taxonomy = 'age' , t2.taxonomy = 'gender' , t3.taxonomy = 'height'

mysql database join