MySQL query to create a new table based on two tables

Question

primary_table:

name_column | description_column
--------------------------------
John        | asb
Marta       | oikjew89
Steve       | fr23

secondary_table:

name_column | lastname_column  | dob_column
-------------------------------------------
Marta       | Doe              | 1970-09-02
Steve       | Dobson           | 1968-04-01
Marta       | Simpson          | 1952-10-08
Michael     | Stone            | 1963-02-22

The the result of the query execution is a result_table table that contains:

name_column | description_column| lastname_column  | dob_column 
---------------------------------------------------------------
John        | asb               |   NULL           |   NULL
Marta       | oikjew89          | Doe              | 1970-09-02
Marta       | oikjew89          | Simpson          | 1952-10-08
Steve       | fr23              | Dobson           | 1968-04-01

which means the result_table is similar to primary_table but wider and has equal or more number of rows. It may get more rows as name_column value may occur more than 1 time in the secondary_table (ex. - Marta).

name_column rows in the secondary_table do not affect on anything if name_column value from the secondary_table is not met in the primary_table (ex. - Steve).

Based on the selection the result_table should be created.

As far as both tables have huge data (thousands of rows), name_column is indexed in both tables so the query execution won't take hours. First and last names are just an example. The actual data is absolutely different from people.

Thank you.

Answer 1

Here's how to create your result_table based on a select statement:

CREATE TABLE result_table AS
SELECT p.name_column, p.description_column, s.lastname_column, s.dob_column
FROM primary_table p
LEFT JOIN secondary_table s ON p.name_column = s.name_column;

Subsequently executing SELECT * FROM result_table ORDER BY name_column; will yield the following output:

+-------------+--------------------+-----------------+------------+
| name_column | description_column | lastname_column | dob_column |
+-------------+--------------------+-----------------+------------+
| John        | asb                | NULL            | NULL       |
| Marta       | oikjew89           | Doe             | 1970-09-02 |
| Marta       | oikjew89           | Simpson         | 1952-10-08 |
| Steve       | fr23               | Dobson          | 1968-04-01 |
+-------------+--------------------+-----------------+------------+

Answer 2

You should look at LEFT OUTER JOIN concept. It will give you all the rows from primary table and any matching rows from secondary table. When there is no matching data, NULL will be reported for query columns which refers to secondary table (i.e lastname_column will be null if there is no entry in secondary table).

Further, new table creation should be simple based on result of above query

Based on your example, query should look like

select t1.name_column, t1.description, t2.lastname_column, t2.dob_column
from primarytable t1
left outer join secondarytable t2 on t1.name_column = t2.name_column

Answer 3

If what you intend is to create new table then you need to read whole schemas of both tables and recreate new table with schemas and insert those record from both tables.

How ever there is simple approach to just query it with out actually create new table which is more simple.

SELECT 
pt.name_column,
pt.description_column,
st.lastname_column,
st.dob_column
FROM primary_table AS pt 
LEFT OUTER JOIN secondary_table AS st 
ON pt.column_name = st.column_name 

Answer 4

you can achieve the required result by using left join in query ...

SELECT A.name_column,A.description_column,B.lasname_column,B.dob_column
from Primary_table A
Left Join Secondary_table B
ON A.name_column = B.name_column