1

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.

2
  • Are the ID columns added with the solutions or do the tables already have them? And what version of SQLite do you use? Commented Nov 1, 2018 at 20:51
  • @ypercubeᵀᴹ : 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.
    – Argus2020
    Commented Nov 1, 2018 at 21:21

2 Answers 2

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

1
  • Sorry, no cigar. I have solved it in a clumsy way (see my answer), and I don't even fully understand why it works. Thanks for your suggestion, but I had tried that before , and many other variations. I would appreciate a more elegant and memorable solution.
    – Argus2020
    Commented Nov 1, 2018 at 18:35
0

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.