SQLite Forum

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

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

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.