SQLite Forum

Wow! Thank you so much for your time and efforts. That query worked first time and shows me all the keywords attached to each title. Exactly what I need. Now my Perl script can capture everything and format it the way I want (this is actually a web project I'm in the middle of making).

<pre>
sqlite> 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
   ...> ;
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
</pre>

The 'docid' is used to identify which document a particular author has written. As you might notice most of the authors only have 1 title, but there happened to be an author with several titles - not an uncommon situation.  I've thought of using authorid and docid as a primary key but that is another fire to put out.

Again many thanks for all your work.