Another newbie query on 2 tables

sqlite

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.

Best Answer

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

Related Solutions

“standard way” to log network messages (events) into a database

It seems that the very same question has been asked (and answered) on stackoveflow:

https://stackoverflow.com/questions/2797592/best-practice-logging-events-general-and-changes-database

SQLite – Time Series Data in One Table or Several? File Size Impact?

Personally, I hate to throw away potentially useful data. I'd create a table for the sources, and include source_id in the time series table as a foreign key to the source table. This should take up less space, but still retain the source information (without requiring multiple identical tables).

I've put together a short example; see this db-fiddle link.

Here's the code form my example:

CREATE TABLE source
     ( source_id INTEGER PRIMARY KEY ASC
      ,name varchar(10)
     );

CREATE TABLE time_series
     ( series_id INTEGER PRIMARY KEY ASC
      ,timestamp INTEGER
      ,value1 INTEGER
      ,value2 INTEGER
      ,value3 INTEGER
      ,source_id INT
      ,FOREIGN KEY (source_id) REFERENCES source(source_id)
     );

INSERT INTO source (name)
VALUES ('AAAA'), ('BBBB'), ('QZQZ');

INSERT INTO time_series (timestamp, value1, value2, value3, source_id)
VALUES (12345678, 100, 105, 110, 1)
      ,(12345681, 105, 105, 105, 1)
      ,(12345684, 110, 105, 100, 1)
      ,(12345678, 9, 27, 81, 3)
      ,(12345681, 27, 81, 243, 3)
      ,(12345684, 81, 243, 729, 3)
;



SELECT * FROM source;

SELECT s.name as source, timestamp, value1, value2, value3
  FROM source s INNER JOIN time_series ts ON (s.source_id = ts.source_id)
 ORDER BY source, timestamp
;

I should note that I don't normally use SQLite. As you noted in your response to my original comment, SQLite maintains a 64-bit integer row ID column in every table by default. I've set up the tables in the example to use that row ID value as the primary key for each table. If I've read the documentation correctly, the foreign key column should just be big enough to hold the value from the primary key. Assuming you don't manually insert a source_id that's huge, I believe that source_id in the time_series table should only require 1 byte.

You might want to put a UNIQUE index on source_id and timestamp (you should almost certainly have some sort of index on them); presumably, you should never have two entries for the same source and the same time. As pointed out by Serge Stroobandt, this could even be the primary key. However, since SQLite will create a unique row ID value anyway, I'd be inclined to leave that as the key. If you really wanted to use source_id and timestamp as the primary key, look into creating the table WITHOUT ROWID.

Best Answer

Related Solutions

“standard way” to log network messages (events) into a database

SQLite – Time Series Data in One Table or Several? File Size Impact?

Related Question