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 | 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!