Merging 2 tables into a table with condition-CodePudding

I have been doing some little project for me to learn SQL. I have 2 tables that i need to merge into a different table with some expectation. See image

//Table 1
create table evaluation(
    [year] varchar(4) not null,
    quarterly  varchar (15) not null,
    indikator  varchar (20) not null,
    subindikator  varchar (100) not null,
    tipe varchar (10) not null,
    [status] varchar(10) not null
);
//Table 2
create table person(
    idNumber varchar(20) not null,
    [name] varchar(20) not null,
    gender char(1) not null
);
//Table 3 Merged From Table 1 and Table 2
create table detail(
    idNumber varchar(20) not null,
    [year] varchar(4) not null,
    quarterly  varchar (15) not null,
    indikator  varchar (20) not null,
    subindikator  varchar (100) not null,
);

i cant find the right keyword on google

Evaluation table:

Table evaluation

Person Table:

Table person

Expected Results:

Table detail what i expected

CodePudding user response：

The simplest way to combine two tables together is using the keywords UNION or UNION ALL. These two methods pile one lot of selected data on top of the other. strong text

SELECT name_column_one, name_column_three FROM name_of_table_one UNION SELECT name_column_one, name_column_three FROM name_of_table_two ;

The difference between the two keywords is that UNION only takes distinct values, but UNION ALL keeps all of the values selected. Both are used with the same syntax.

SELECT 
    name_column_one, 
    name_column_three
FROM
    name_of_table_one

UNION ALL SELECT name_column_one, name_column_three FROM name_of_table_two ;

CodePudding user response：

With a cross-join the SELECT statement would look like:

SELECT t1.idNumber, 
   t2.[year],
   t2.quarterly,
   t2.indikator,
   t2.subindikator
FROM evaluation, person;

Putting that into an INSERT statement to load your merged table:

INSERT INTO merged
SELECT t1.idNumber, 
   t2.[year],
   t2.quarterly,
   t2.indikator,
   t2.subindikator
FROM evaluation, person

Instead of that comma between evaluation and person tables in the FROM clause you can also use the CROSS JOIN keyword like FROM evaluaation CROSS JOIN person.