Home >Database >Mysql Tutorial >How to Merge Data from Multiple MySQL Tables into a New Table?

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

Susan Sarandon
Susan SarandonOriginal
2024-11-23 13:11:141033browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn