How to fill and join 3 sub tables with data contained in an other main table

Question

I have the following main_table, already filled with data:

From this main_table I want to fill and join 3 sub-tables (which are empty) with data contained in the main_table using foreign key, in order to get something like that:

I have difficulty to understand which SQL code is needed to fill the 3 sub-tables, and to generate the join between them.

I tried to use the following code, but it does not create any junction between tables. I imagine I have to use "JOIN ... ON" somewhere, but I've difficulty to understand how should I do it.

INSERT INTO table_town
    (name_town)
SELECT name_town
FROM table_main
GROUP BY name_town
ORDER BY name_town;

Data for a reproducible example:

Schema (MySQL v5.7)

CREATE TABLE table_main (
  id INT NOT NULL AUTO_INCREMENT,
  price INT NULL DEFAULT NULL,
  house VARCHAR(30) NULL DEFAULT NULL,
  name_town VARCHAR(30) NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO table_main 
(price  , house ,   name_town       ) 
VALUES 
(100    ,   'A' ,   'DAISYTOWN'     ),
(1500   ,   'C' ,   'PRICETOWN'     ),
(400    ,   'B',    'QUEENSTOWN'    ),
(200    ,   'D',    'JACKSTOWN'     ),
(150    ,   'E',    'JACKSTOWN'     ),
(400    ,   'F',    'PRICETOWN'     ),
(200    ,   'A',    'DAISYTOWN'     ),
(700    ,   'B',    'QUEENSTOWN'    ),
(800    ,   'G',    'QUEENSTOWN'    ),
(2500   ,   'H',    'HYPERTOWN'     )
;

CREATE TABLE table_town (
  id INT NOT NULL AUTO_INCREMENT,
  name_town VARCHAR(30) NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE table_house (
  id INT NOT NULL AUTO_INCREMENT,
  table_town_id INT NOT NULL,
  house VARCHAR(30) NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE table_sale_estate (
  id INT NOT NULL AUTO_INCREMENT,
  table_house_id INT NOT NULL,
  price INT NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

ALTER TABLE table_sale_estate ADD CONSTRAINT `table_sale_estate_fk_table_house` FOREIGN KEY (`table_house_id`) REFERENCES `table_house`(`id`);

ALTER TABLE table_house ADD CONSTRAINT `table_house_fk_table_town` FOREIGN KEY (`table_town_id`) REFERENCES `table_town`(`id`);

Query #1

SELECT * FROM table_main;

id	price	house	name_town
1	100	A	DAISYTOWN
2	1500	C	PRICETOWN
3	400	B	QUEENSTOWN
4	200	D	JACKSTOWN
5	150	E	JACKSTOWN
6	400	F	PRICETOWN
7	200	A	DAISYTOWN
8	700	B	QUEENSTOWN
9	800	G	QUEENSTOWN
10	2500	H	HYPERTOWN

Query #2

INSERT INTO table_town
    (name_town)
SELECT name_town
FROM table_main
GROUP BY name_town
ORDER BY name_town;

Query #3

SELECT * FROM table_town;

id	name_town
1	DAISYTOWN
2	HYPERTOWN
3	JACKSTOWN
4	PRICETOWN
5	QUEENSTOWN

View on DB Fiddle

I'm trying to fill the 3 sub-tables with data coming from the table_main, making sure that relations between sub-tables across foreign keys are working without losing data.

As shown above, I've tried to use INSERT INTO ... SELECT ... FROM ... to fill the table_town.

I suppose I should now use a JOIN ... ON clause to fill and join the 3 sub-tables correctly, but I have some difficulties to understand how should I write the correct code to do it, and in which order should I do it.

I'm beginner in SQL. I will do my best to understand.

user_10498828 · Answer

First, you must fill the table table_town from table_main, but there is no need for aggregation.
All you need is the distinct values of name_town:

INSERT INTO table_town (name_town)
SELECT DISTINCT name_town
FROM table_main
ORDER BY name_town;

Then, fill table_house by joining table_main to table_town:

INSERT INTO table_house(table_town_id, house)
SELECT DISTINCT t.id, m.house
FROM table_main m INNER JOIN table_town t
ON t.name_town = m.name_town
ORDER BY m.house;

Finally fill table_sale_estate by joining table_main to table_house:

INSERT INTO table_sale_estate(table_house_id, price)
SELECT h.id, m.price
FROM table_main m INNER JOIN table_house h
ON h.house = m.house 
ORDER BY m.id;

See the demo.

Note that the ORDER BY clauses I use in my code, although they seem to work, they do not guarantee the order of the rows in the tables because there is no such order.

How to fill and join 3 sub tables with data contained in an other main table

1 Answers