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 :
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.