Another newbie query on 2 tables

Question

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.

Answer 1

Try:

INSERT INTO T2 
SELECT NULL,T1.KEY,NULL 
FROM T1 
LEFT JOIN T2 USING (key,id)
WHERE T2.KEY IS NULL;

This should filter your join to select only those rows where there is currently no data value in T2.

(This is your original attempt, plus a new WHERE clause).

Answer 2

I believe this a solution,but without a nod to performance or elegance. It uses a secondary table to allow the final report to show correct output, and adds dummy records to ensure that both tables have the same number of records. I would appreciate a less clumsy way of doing this.

CREATE TABLE T1(id INTEGER PRIMARY KEY AUTOINCREMENT,key,data);
CREATE TABLE T2(id INTEGER PRIMARY KEY AUTOINCREMENT,key,data);
INSERT INTO  T1 VALUES -- LOAD T1 TEST DATA
 (NULL,"A","T1A1")
,(NULL,"A","T1A2")
,(NULL,"A","T1A3")
,(NULL,"B","T1B1")
,(NULL,"B","T1B2")
,(NULL,"B","T1B3");
INSERT INTO  T2 VALUES -- LOAD T2 TEST DATA
 (NULL,"A","T2A1")
,(NULL,"B","T2B1")
,(NULL,"C","T2C2")
,(NULL,"C","T2C1");
INSERT INTO  T1 SELECT NULL,key,NULL FROM T2 WHERE  id NOT IN (SELECT id FROM T1) OR key NOT IN (SELECT key FROM T1); -- PAD T1
INSERT INTO  T2 SELECT NULL,key,NULL FROM T1 WHERE  id NOT IN (SELECT id FROM T2) OR key NOT IN (SELECT key FROM T2); -- PAD T2
INSERT INTO  T1 SELECT NULL,key,NULL FROM T2 WHERE  id NOT IN (SELECT id FROM T1) OR key NOT IN (SELECT key FROM T1); -- PAD T1
CREATE TABLE T3(id INTEGER PRIMARY KEY AUTOINCREMENT, key,data); -- TEMP TABLE
INSERT INTO  T3 SELECT NULL,key,data FROM T2 ORDER BY key,data ; -- RENUMBER T2
CREATE TABLE T4(id INTEGER PRIMARY KEY AUTOINCREMENT, key,data); -- TEMP TABLE
INSERT INTO  T4 SELECT NULL,key,data FROM T1 ORDER BY key,data ; -- SORT & RENUMBER T1 as T4
.nullvalue '----'
.mode column
.header on
.width 2 5 5 5
SELECT 
  T4.id AS ID
, T4.key AS KEY
, T4.data AS T1
, T3.data AS T2 
FROM T4 
LEFT JOIN T3 USING (id,key);

-- ID  KEY    T1     T2   
-- --  -----  -----  -----
-- 1   A      T1A1   T2A1 
-- 2   A      T1A2   ---- 
-- 3   A      T1A3   ---- 
-- 4   B      T1B1   T2B1 
-- 5   B      T1B2   ---- 
-- 6   B      T1B3   ---- 
-- 7   C      ----   T2C1 
-- 8   C      ----   T2C2