How to Merge Data from Multiple MySQL Tables into a New Table?

Merging Data from Multiple Tables into a New Table Using MySQL

Problem:

Create a new MySQL table that combines specific data and columns from three existing tables: people, taxonomy, and details. The goal is to create a table that includes selected attributes such as last name, first name, email, and age.

Desired Table Structure:

ID	Last Name	First Name	Email	Age
1	Smith	Fred	Fred@..	36
2	Jones	Tom	Tom@..	29
3	Doe	Jane	Jane@..	27

Solution:

To accomplish this, perform a 3-way JOIN on the three tables:

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 query combines data from the people, details, and taxonomy tables based on matching IDs. It selects the desired columns (id, last_name, first_name, email, and age) from the joined tables.

Alternative Method:

If you have already created the new_table table, you can insert data into it using the following query:

INSERT INTO new_table (id, last_name, first_name, email, age)
SELECT p.id, p.last_name, p.first_name, p.email, 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';

Multiple Attributes:

To include multiple attributes from the three tables, you can 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';

By employing multiple JOINs, you can merge data and columns from various tables into a comprehensive new table.

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