as result of complicated select I have the_table with the structure like:
<code>pl_id, date, value
</code>
<code>pl_id, date, value
</code>
pl_id, date, value
I also have another table (let it calls the_source) where I have a list of all vailable pl_id.
The idea is to extend the_table with values from the_source which doesn’t exist.
The problem is that some columns must be defined exactly as expected, another – can be any (let it be 0).
<code>CREATE TABLE the_table (
"pl_id" INTEGER,
"date" DATE,
"value" INTEGER
);
INSERT INTO the_table
VALUES
('1', '2024-05-01', '10'),
('1', '2024-06-01', '11'),
('2', '2024-05-01', '12'),
('2', '2024-06-01', '13');
CREATE TABLE the_source (
"pl_id" INTEGER,
"msg" TEXT
);
INSERT INTO the_source
VALUES
('1', 'aaaaa'),
('2', 'bbbbb'),
('3', 'ccccc'),
('4', 'ddddd');
</code>
<code>CREATE TABLE the_table (
"pl_id" INTEGER,
"date" DATE,
"value" INTEGER
);
INSERT INTO the_table
VALUES
('1', '2024-05-01', '10'),
('1', '2024-06-01', '11'),
('2', '2024-05-01', '12'),
('2', '2024-06-01', '13');
CREATE TABLE the_source (
"pl_id" INTEGER,
"msg" TEXT
);
INSERT INTO the_source
VALUES
('1', 'aaaaa'),
('2', 'bbbbb'),
('3', 'ccccc'),
('4', 'ddddd');
</code>
CREATE TABLE the_table (
"pl_id" INTEGER,
"date" DATE,
"value" INTEGER
);
INSERT INTO the_table
VALUES
('1', '2024-05-01', '10'),
('1', '2024-06-01', '11'),
('2', '2024-05-01', '12'),
('2', '2024-06-01', '13');
CREATE TABLE the_source (
"pl_id" INTEGER,
"msg" TEXT
);
INSERT INTO the_source
VALUES
('1', 'aaaaa'),
('2', 'bbbbb'),
('3', 'ccccc'),
('4', 'ddddd');
missed records can be found by select:
<code>select * from the_source
where not exists (
select
*
from the_table
where the_table.pl_id = the_source.pl_id)
</code>
<code>select * from the_source
where not exists (
select
*
from the_table
where the_table.pl_id = the_source.pl_id)
</code>
select * from the_source
where not exists (
select
*
from the_table
where the_table.pl_id = the_source.pl_id)
I would like to have the_final table with folling information:
<code> ('1', '2024-05-01', '10'),
('1', '2024-06-01', '11'),
('2', '2024-05-01', '12'),
('2', '2024-06-01', '13')
('3', '2024-05-01', '0'),
('3', '2024-06-01', '0'),
('4', '2024-05-01', '0'),
('4', '2024-06-01', '0');
</code>
<code> ('1', '2024-05-01', '10'),
('1', '2024-06-01', '11'),
('2', '2024-05-01', '12'),
('2', '2024-06-01', '13')
('3', '2024-05-01', '0'),
('3', '2024-06-01', '0'),
('4', '2024-05-01', '0'),
('4', '2024-06-01', '0');
</code>
('1', '2024-05-01', '10'),
('1', '2024-06-01', '11'),
('2', '2024-05-01', '12'),
('2', '2024-06-01', '13')
('3', '2024-05-01', '0'),
('3', '2024-06-01', '0'),
('4', '2024-05-01', '0'),
('4', '2024-06-01', '0');
Is it possible realize by SQL commands?