I'm having trouble working out this apparently simple query, and would appreciate any suggestions for a solution using Sqlite.
I have two tables t1 and t2 :
Eg.,
Table1 :
--------
Key Data
A data11
A data12
A data13
B data21
B data22
B data23
Table2 :
--------
Key Data
A datax
B datay
And I want to pad out Table2 to be like this :
Table2 :
--------
Key Data
A datax
A null
A null
B datay
B null
B null
Ie., So that both tables have the same number of entries for each Key value.
CREATE TABLE T1(key,data);
CREATE TABLE T2(key,data);
INSERT INTO T1 VALUES ("A","data11"),("A","data12"),("A","data13"), ("B","data21"),("B","data22"),("B","data23");
INSERT INTO T2 VALUES ("A","datax"),("B","datay");
I have tried the following :
CREATE TABLE T1(id integer primary key autoincrement,key,data);
CREATE TABLE T2(id integer primary key autoincrement,key,data);
INSERT INTO T2 SELECT NULL,T1.KEY,NULL FROM T1 LEFT JOIN T2 USING (key,id);
This adds too many rows (6 instead of 4). There should be the same number of rows in both tables.
|1|A|datax
|2|B|datay
|3|A|Null
|4|A|Null
|5|A|Null
|6|B|Null
|7|B|Null
|8|B|Null
The additional records are just padding so that both tables have the same number of rows. This simplifies the task creating the report where there are a variable number of items per key per category. I didn't know how else to do it, and it took quite a while to come up with this solution.
The ID columns are autoincremented as the tables are populated. I have Sqlite 3.13.0. In reality , the 'key' field are project-codes and the 'data' field are object names from 2 repositories. The report is supposed to show what objects are active for a given project and on which repository.