I'm having 4 different tables:
macro_area(primary key is "id")
micro_area(primary key is "id"; foreign key is macro_area_id referencing macro_area(id))
user(primary key is "id"; foreign key is macro_area_id referencing macro_area(id))
and finally
user_micro_area(primary key is "id" , foreign keys are: micro_area_id referencing micro_area(id), macro_area_id referencing macro_area(id) and user_id referencing user(id))
The user_micro_area table is establishing the relation between micro_area table and the user table(this is how I received the db)
This is the loading script:
LOAD id as micro_area_id,
version as micro_area_version,
codice as micro_area_codice,
descrizione as micro_area_descrizione,
`macro_area_id`;
SQL SELECT *
FROM alfaintes.`micro_area`;
LOAD id as user_micro_area_id,
version as user_micro_area_version,
`macro_area_id`,
`micro_area_id`,
`user_id`;
SQL SELECT *
FROM alfaintes.`user_micro_area`;
LOAD codice as macro_area_codice,
id as macro_area_id;
SQL SELECT codice,
id
FROM alfaintes.`macro_area`;
LOAD cognome as Cognome,
id as user_id,
nome as Nome,
macro_area_id,
username,
enabled,
ruolo_id as role;
SQL SELECT cognome,
id,
nome,
macro_area_id,
username,
enabled,
ruolo_id
FROM alfaintes.`user`;
When I run the loading script, it generates a synthetic table that has more records than it should be. How should I tackle this issue?
I have attached a screenshot of the tables' relation
