How to Combine Data from Three MySQL Tables into a New Table Using JOINs?

Combining Data from Multiple Tables into a New Table with MySQL

When working with relational databases, often you need to combine data from multiple tables into a new table. This question demonstrates how to achieve this using a 3-way JOIN in MySQL.

Objective: Create a new table that includes specific data and columns from three existing tables (people, taxonomy, and details).

Existing Tables:

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"

Desired Result (New Table):

id  last_name  first_name  email   age
1   Smith       Fred        Fred@.. 36
2   Jones       Tom         Tom@..  29
3   Doe         Jane        Jane@.. 27

Solution using a 3-Way JOIN:

To create the new table, we need to perform a 3-way JOIN to connect the three existing tables based on common values:

CREATE TABLE new_table AS
SELECT p.*, d.content AS age
FROM people AS p
JOIN details AS d ON d.person_id = p.id
JOIN taxonomy AS t ON t.id = d.detail_id
WHERE t.taxonomy = 'age';

This JOIN combines the data from the people table with the details table based on the person_id column, and then further joins the details table with the taxonomy table based on the detail_id column, filtering only for rows where the taxonomy is 'age'.

Alternate Approach (for Multiple Attributes):

To include multiple attributes (such as age, gender, and height) from the details table, you need to perform separate JOINs for each attribute:

CREATE TABLE new_table AS
SELECT p.*, d1.content AS age, d2.content AS gender, d3.content AS height
FROM people AS p
JOIN details AS d1 ON d1.person_id = p.id
JOIN taxonomy AS t1 ON t1.id = d1.detail_id
JOIN details AS d2 ON d2.person_id = p.id
JOIN taxonomy AS t2 ON t2.id = d2.detail_id
JOIN details AS d3 ON d3.person_id = p.id
JOIN taxonomy AS t3 ON t3.id = d3.detail_id
WHERE t1.taxonomy = 'age' AND t2.taxonomy = 'gender' AND t3.taxonomy = 'height';

This approach allows you to combine data from multiple columns in the details table into the new table.

The above is the detailed content of How to Combine Data from Three MySQL Tables into a New Table Using JOINs?. For more information, please follow other related articles on the PHP Chinese website!