Thank you, this helps.
My mistake was actually less of an SQL fault and more a misunderstanding of your original schema.
WHat use is "docid" - it seems to just always be 1. (I mistakenly thought it was an FK into "id" of the "document" table).
Anyway, armed with this new information, I had made the changes and it seems to work fine. I include here the entire script in case anyone wishes to reproduce it.
The added queries are at the end - I include both examples of getting the fully expanded list, and also the grouped list with concatenated keywords.
```
-- SQLite version 3.30.1 [ Release: 2019-10-10 ] on SQLitespeed version 2.1.3.11.
===============================================================================
CREATE TABLE IF NOT EXISTS "docs" (
id integer not null primary key autoincrement,
authorid integer,
docid integer,
title text,
source text,
pubyear integer,
notes text,
author text,
filename text
);
INSERT INTO docs VALUES
(1,501,1,'Ocean Viruses','Quanta Magazine',2019,NULL,'Lambert,Jonathan','Ocean Viruses.pdf'),
(2,649,1,'20 Things You Didn''t Know About Glaciers','Discover',2020,NULL,'Keats,Jonathan','20 Things You Didn''t Know About Glaciers.pdf'),
(3,650,1,'Crystal Colony','Discover',2020,NULL,'Walter,Jennifer','Crystal Colony.pdf'),
(4,651,1,'Smarter Cities','MIT Technology Review',2020,NULL,'Yang,Lynne Pesko','Smarter Cities.pdf'),
(5,652,1,'Scientist in Toyland','Discover',2020,NULL,'Ornes,Stephen','Scientist in Toyland.pdf'),
(6,502,1,'The Sixth Extinction','Canadian Geographic',2020,NULL,'Anthony,Leslie;Haasdyk,Mary','The Sixth Extinction.pdf'),
(20,653,1,'Engineering A Culture of Privacy','IEEE Consumer Electronics',2020,'','Kosmala,Peter B.','Engineering A Culture of Privacy.pdf'),
(26,654,1,'All Puffed Up','www.admin-magazine.com',2020,NULL,'Hein,Matthias','All Puffed Up.pdf'),
(27,503,1,'Move','New Scientist',2020,NULL,'Williams,Caroline','Move.pdf'),
(28,656,1,'Covid-19''s AI Revolution','New Scientist',2020,NULL,'Ong,Sandy','Covid-19''s AI Revolution.pdf'),
(29,657,1,'DDoS State Of Affairs','IEEE Computer',2020,NULL,'Osterweil,Eric;Stavrou,Angelos;Zhang,Lixia','DDoS State Of Affairs.pdf'),
(30,671,1,'Double-edged Sword','www.admin-magazine.com',2020,NULL,'Gerling,Rainer W.','Double-edged Sword.pdf'),
(31,999,1,'Evolving Evolution','New Scientist',2020,NULL,'Various','Evolving Evolution.pdf'),
(32,660,1,'Feeling Queasy','New Scientist',2020,NULL,'Thompson,Helen','Feeling Queasy.pdf'),
(33,654,2,'Fleet of Foot','www.admin-magazine.com',2020,NULL,'Hein,Matthias','Fleet of Foot.pdf'),
(34,504,1,'Just A Second','New Scientist',2020,NULL,'Nuwer,Rachel','Just A Second.pdf'),
(35,507,1,'New Top in Town','www.admin-magazine.com',2020,NULL,'Layton,Jeff','New Top in Town.pdf'),
(36,672,1,'Pass The Test','www.admin-magazine.com',2020,NULL,'Wubbeling,Matthias','Pass The Test.pdf'),
(37,658,1,'Relationship Status','www.admin-magazine.com',2020,NULL,'Dodlek,Zeljko','Relationship Status.pdf'),
(38,659,1,'Relationships','www.admin-magazine.com',2020,NULL,'Brendel,Jens-Christoph','Relationships.pdf'),
(39,670,1,'The Ultimate Battery','New Scientist',2020,NULL,'Hambling,David','The Ultimate Battery.pdf'),
(40,1000,1,'The Universe According to Emmy Noether','Discover 2017-06',2017,'','Nadis,Steve','The Universe According to Emmy Noether.pdf'),
(41,1001,1,'A Profound Plan To Save the Seas','Science 2020-10',2020,NULL,'Hannibal,Mary Ellen','A Profound Plan.pdf'),
(42,1002,1,'A Technology of Kinetic Art','Scientific American 1993-02',1993,NULL,'Rickey,George','A Technology of Kinetic Art.pdf'),
(43,1003,1,'Context Is Key to Olfaction','Science 2020-07',2020,NULL,'McGann,John P.','Context Is Key to Olfaction.pdf'),
(44,1004,1,'Lizard Man','Science 2020-07',2020,NULL,'Pennisi,Elizabeth','Lizard Man.pdf'),
(45,1005,1,'Sean Connery','Scotland Magazine 2021-01',2021,NULL,'Rowe,Jenny;Adolph,Anthony','Sean Connery.pdf'),
(46,1006,1,'The Talented Luthier','Scotland Magazine 2021-01',2021,NULL,'Gilchrist,Jim','The Talented Luthier.pdf'),
(47,1007,1,'Alphabet''s Now Building Cities','Wired 2018-01-02',2018,NULL,'Wright,Emily','Alphabet Cities.pdf'),
(48,1008,1,'A Partly True Story','Scientific American 1993-02',1993,NULL,'Stewart,Ian','Partly True Story.pdf'),
(49,1009,1,'Redeeming Charles Babbage''s Mechanical Computer','Scientific American 1993-02',1993,NULL,'Swade,Doron D.','Babbage.pdf');
CREATE TABLE authors (
id integer not null primary key autoincrement,
author text,
authorid integer,
docid integer
);
INSERT INTO authors VALUES
(1,'Lambert,Jonathan',501,1),
(2,'Keats,Jonathan',649,1),
(3,'Walter,Jennifer',650,1),
(4,'Yang,Lynne Pesko',651,1),
(5,'Ornes,Stephen',652,1),
(6,'Anthony,Leslie;Haasdyk,Mary',502,1),
(20,'Kosmala,Peter B.',653,NULL),
(26,'Hein,Matthias',654,1),
(28,'Ong,Sandy',656,1),
(29,'Osterweil,Eric;Stavrou,Angelos;Zhang,Lixia',657,1),
(30,'Gerling,Rainer W.',671,1),
(32,'Thompson,Helen',660,1),
(34,'Nuwer,Rachel',504,1),
(35,'Layton,Jeff',507,1),
(36,'Wubbeling,Matthias',672,1),
(37,'Dodlek,Zeljko',658,1),
(38,'Brendel,Jens-Christoph',659,1),
(39,'Hambling,David',670,1),
(40,'Williams,Caroline',503,1),
(42,'Various',999,1),
(43,'Nadis,Steve',1000,NULL),
(44,'Hannibal,Mary Ellen',1001,1),
(45,'Rickey,George',1002,1),
(46,'McGann,John P.',1003,1),
(47,'Pennisi,Elizabeth',1004,1),
(48,'Rowe,Jenny;Adolph,Anthony',1005,1),
(49,'Gilchrist,Jim',1006,1),
(50,'Wright,Emily',1007,1),
(51,'Stewart,Ian',1008,1),
(52,'Swade,Doron D.',1009,1);
CREATE TABLE keywords (
id integer not null primary key autoincrement,
keyword text,
authorid integer,
docid integer
);
INSERT INTO keywords VALUES
(1,'Marine Virus',501,1),
(2,'Oceanography',501,1),
(3,'Biodiversity',649,1),
(4,'Glaciers',649,1),
(5,'Geology',649,1),
(6,'Climate',649,1),
(7,'Bees',650,1),
(8,'Mathematics',650,1),
(9,'Computer Networks',651,1),
(10,'Urban Development',651,1),
(11,'Geometry',652,1),
(12,'Architecture',652,1),
(13,'Kinematic Sculpture',652,1),
(14,'Species Conservation',502,1),
(27,'Computer Networks',657,1),
(28,'DNS Encryption',671,1),
(29,'Network Security',654,1),
(30,'Network Privacy',653,1),
(31,'Computer Networks',671,1),
(32,'Computer Networks',654,1),
(33,'Network Security',657,1),
(34,'Artificial Intelligence',656,1),
(35,'Coronavirus Pandemic',656,1),
(36,'Human Vestibular System',503,1),
(37,'Human Cognitive Behavior',503,1),
(38,'TCP/IP Maintenance',654,1),
(39,'TCP/IP Bufferbloat',654,1),
(40,'DNS Maintenance',671,1),
(41,'Evolution',999,1),
(42,'Battery Power',670,1),
(43,'Energy Conservation',504,1),
(44,'Nuclear Power',670,1),
(45,'Regular Expressions',672,1),
(46,'Programming',672,1),
(47,'Shell Programming',507,1),
(48,'Bash Shell',507,1),
(49,'Horology',504,1),
(50,'Climate',502,1),
(51,'Biodiversity',502,1),
(52,'Wi-Fi Networking',654,2),
(53,'Database Design',658,1),
(54,'Graph Database',658,1),
(55,'Database Design',659,1),
(56,'Graph Database',659,1),
(57,'Mathematics',1000,1),
(58,'Physics',1000,1),
(59,'Marine Conservation',1001,1),
(60,'Oceanography',1001,1),
(61,'Art',1002,1),
(62,'Kinematics',1002,1),
(63,'Physics',1002,1),
(64,'Olfaction',1003,1),
(65,'Neuroscience',1003,1),
(66,'Smell',1003,1),
(67,'Evolution',1004,1),
(68,'Lizards',1004,1),
(69,'Sean Connery',1005,1),
(70,'Biography',1005,1),
(71,'Scottish Ancestry',1005,1),
(72,'Music - Violin',1006,1),
(73,'Luthier',1006,1),
(74,'Art',1006,1),
(75,'Human Vestibular System',660,1),
(76,'Human Balance',660,1),
(77,'Motion Sickness',660,1),
(78,'Smart City',1007,1),
(79,'Urban Design',1007,1),
(80,'Mathematics',1008,1),
(81,'Fuzzy Logic',1008,1),
(82,'Computer Technology',1009,1),
(83,'Engineering',1009,1),
(84,'Difference Engine',1009,1);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('docs',49);
INSERT INTO sqlite_sequence VALUES('authors',52);
INSERT INTO sqlite_sequence VALUES('keywords',84);
SELECT id,authorid,docid,title,source
FROM docs
ORDER BY id DESC
LIMIT 5
;
-- id |authorid|docid|title |source
-- ----|--------|-----|-------------------------------------------------|-----------------------------
-- 49 | 1009 | 1 |Redeeming Charles Babbage's Mechanical Computer |Scientific American 1993-02
-- 48 | 1008 | 1 |A Partly True Story |Scientific American 1993-02
-- 47 | 1007 | 1 |Alphabet's Now Building Cities |Wired 2018-01-02
-- 46 | 1006 | 1 |The Talented Luthier |Scotland Magazine 2021-01
-- 45 | 1005 | 1 |Sean Connery |Scotland Magazine 2021-01
SELECT authorid,docid,keyword
FROM keywords
WHERE authorid IN (1005,1006,1007,1008,1009)
ORDER BY id DESC
;
-- authorid|docid|keyword
-- --------|-----|---------------------
-- 1009 | 1 |Difference Engine
-- 1009 | 1 |Engineering
-- 1009 | 1 |Computer Technology
-- 1008 | 1 |Fuzzy Logic
-- 1008 | 1 |Mathematics
-- 1007 | 1 |Urban Design
-- 1007 | 1 |Smart City
-- 1006 | 1 |Art
-- 1006 | 1 |Luthier
-- 1006 | 1 |Music - Violin
-- 1005 | 1 |Scottish Ancestry
-- 1005 | 1 |Biography
-- 1005 | 1 |Sean Connery
WITH D AS (
SELECT id, authorid, docid, title, source
FROM docs
ORDER BY id DESC
LIMIT 5
)
SELECT D.id, D.authorid, D.docid, D.title, A.author, D.source, K.keyword
FROM D
JOIN authors AS A ON A.docid = D.docid AND A.authorid = D.authorid
JOIN keywords AS K ON K.docid = D.docid AND K.authorid = D.authorid
;
-- id |authorid|docid|title |author |source |keyword
-- ----|--------|-----|-------------------------------------------------|---------------------------|-----------------------------|---------------------
-- 49 | 1009 | 1 |Redeeming Charles Babbage's Mechanical Computer |Swade,Doron D. |Scientific American 1993-02 |Computer Technology
-- 49 | 1009 | 1 |Redeeming Charles Babbage's Mechanical Computer |Swade,Doron D. |Scientific American 1993-02 |Difference Engine
-- 49 | 1009 | 1 |Redeeming Charles Babbage's Mechanical Computer |Swade,Doron D. |Scientific American 1993-02 |Engineering
-- 48 | 1008 | 1 |A Partly True Story |Stewart,Ian |Scientific American 1993-02 |Fuzzy Logic
-- 48 | 1008 | 1 |A Partly True Story |Stewart,Ian |Scientific American 1993-02 |Mathematics
-- 47 | 1007 | 1 |Alphabet's Now Building Cities |Wright,Emily |Wired 2018-01-02 |Smart City
-- 47 | 1007 | 1 |Alphabet's Now Building Cities |Wright,Emily |Wired 2018-01-02 |Urban Design
-- 46 | 1006 | 1 |The Talented Luthier |Gilchrist,Jim |Scotland Magazine 2021-01 |Art
-- 46 | 1006 | 1 |The Talented Luthier |Gilchrist,Jim |Scotland Magazine 2021-01 |Luthier
-- 46 | 1006 | 1 |The Talented Luthier |Gilchrist,Jim |Scotland Magazine 2021-01 |Music - Violin
-- 45 | 1005 | 1 |Sean Connery |Rowe,Jenny;Adolph,Anthony |Scotland Magazine 2021-01 |Biography
-- 45 | 1005 | 1 |Sean Connery |Rowe,Jenny;Adolph,Anthony |Scotland Magazine 2021-01 |Scottish Ancestry
-- 45 | 1005 | 1 |Sean Connery |Rowe,Jenny;Adolph,Anthony |Scotland Magazine 2021-01 |Sean Connery
WITH D AS (
SELECT id, authorid, docid, title, source
FROM docs
ORDER BY id DESC
LIMIT 5
)
SELECT D.id, MAX(D.title) AS Title, MAX(A.author) AS Author, MAX(D.source) AS Source,
GROUP_CONCAT(K.keyword) AS Keywords
FROM D
JOIN authors AS A ON A.docid = D.docid AND A.authorid = D.authorid
JOIN keywords AS K ON K.docid = D.docid AND K.authorid = D.authorid
GROUP BY D.id
ORDER BY D.id DESC
;
-- id |Title |Author |Source |Keywords
-- ----|-------------------------------------------------|---------------------------|-----------------------------|---------------------------------------------------
-- 49 |Redeeming Charles Babbage's Mechanical Computer |Swade,Doron D. |Scientific American 1993-02 |Computer Technology,Difference Engine,Engineering
-- 48 |A Partly True Story |Stewart,Ian |Scientific American 1993-02 |Fuzzy Logic,Mathematics
-- 47 |Alphabet's Now Building Cities |Wright,Emily |Wired 2018-01-02 |Smart City,Urban Design
-- 46 |The Talented Luthier |Gilchrist,Jim |Scotland Magazine 2021-01 |Art,Luthier,Music - Violin
-- 45 |Sean Connery |Rowe,Jenny;Adolph,Anthony |Scotland Magazine 2021-01 |Biography,Scottish Ancestry,Sean Connery
-- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.044s
-- Total Script Query Time: 0d 00h 00m and 00.003s
-- Total Database Rows Changed: 141
-- Total Virtual-Machine Steps: 6145
-- Last executed Item Index: 14
-- Last Script Error:
-- -------------------------------------------------------------------------------
-- 2021-01-10 02:45:37.922 | [Success] Script Success.
-- 2021-01-10 02:45:37.928 | [Success] Transaction Rolled back.
===============================================================================
```
Hope that clears things up - but feel free to ask if any of it needs further explaining.
Good luck!