mySQL: Creating a New Table from Data and Columns of Three Tables

Question:

How can I create a new table that combines selected data from three existing tables, including the fields last_name, first_name, email, and age from the people, details, and taxonomy tables?

Answer:

To achieve this, you can utilize a 3-way JOIN.

Creating a New Table with Joining:

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 will create a new table named new_table that includes the last_name, first_name, email, and age columns. In the details table, the content field is used to store the age information.

Inserting Data into an Existing Table:

If you have already created the new_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';

Joining for Multiple Attributes:

To retrieve multiple attributes (e.g., age, gender, height) from the other tables, you will need to perform additional joins:

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 AND d1.taxonomy_id = (SELECT id FROM taxonomy WHERE taxonomy = 'age')
JOIN details AS d2 ON d2.person_id = p.id AND d2.taxonomy_id = (SELECT id FROM taxonomy WHERE taxonomy = 'gender')
JOIN details AS d3 ON d3.person_id = p.id AND d3.taxonomy_id = (SELECT id FROM taxonomy WHERE taxonomy = 'height');