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

Creating a New Table from Multiple Existing Tables in MySQL

To create a new table that combines data and columns from multiple existing tables, you can use the JOIN operation. In MySQL, this can be achieved through the following steps:

1. Join the Tables: Use the JOIN keyword to connect the tables based on common fields. For example, to join the people, taxonomy, and details tables based on the person_id field:

SELECT *
FROM people AS p
JOIN details AS d ON p.id = d.person_id
JOIN taxonomy AS t ON d.detail_id = t.id

2. Select Desired Columns: Specify the columns you want to include in the new table using the SELECT statement. For instance, if you want to create a new table with id, last_name, first_name, email, and age, you would modify the query to:

SELECT p.id, p.last_name, p.first_name, p.email, d.content AS age
FROM people AS p
JOIN details AS d ON p.id = d.person_id
JOIN taxonomy AS t ON d.detail_id = t.id
WHERE t.taxonomy = 'age'

3. Create the New Table: Execute the CREATE TABLE statement to create a new table with the specified columns and data. For instance, to create a new table named new_table based on the previous query:

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

4. Alternatively, Insert into Existing Table: If you have already created the new_table, you can use the INSERT statement to populate it with data from the joined tables:

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 p.id = d.person_id
JOIN taxonomy AS t ON d.detail_id = t.id
WHERE t.taxonomy = 'age'

By using these methods, you can create new tables that combine data and columns from multiple existing tables in MySQL, providing a convenient and efficient way to organize and access information.

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