I’m not at a place where I can test to make sure I have syntax correct, but it sounds like you might want to do it as a ‘sub query’ rather than a join, as you don’t have a one-to-one relationship.
(So the subquery would do the concat, and you don’t risk it expanding multiplicatively because of the multiple one-to-many joins) You would need two sub-queries, one for each left join. -Joe Sent from a mobile device with a crappy on screen keyboard and obnoxious "autocorrect" > On Oct 29, 2021, at 10:23 AM, Stefano Bargioni <bargi...@pusc.it> wrote: > > Hi, Eric: is this page useful for you? > https://www.sqlitetutorial.net/sqlite-union/ > <https://www.sqlitetutorial.net/sqlite-union/> > SB > >> On 29 Oct 2021, at 15:46, Eric Lease Morgan <emor...@nd.edu> wrote: >> >> How do I join multiple tables in an SQLite database in order to output the >> values in the linked tables? >> >> >> I have a database with the following structure: >> >> create table bibliographics ( >> identifier TEXT PRIMARY KEY, >> title TEXT >> ); >> >> create table keywords ( >> identifier TEXT, >> keyword TEXT >> ); >> >> create table entities ( >> identifier TEXT, >> entity TEXT >> ); >> >> >> I want output looking like this: >> >> identifier = homer >> keywords = love; honor; truth; justice; >> entities = jove; troy; helen; son; ship >> >> >> Here is my SQL query: >> >> SELECT b.identifier, >> GROUP_CONCAT( e.entity, '; ' ) AS entities, >> GROUP_CONCAT( k.keyword, '; ' ) AS keywords >> FROM bibliographics AS b >> LEFT JOIN entities AS e ON e.identifier IS b.identifier >> LEFT JOIN keywords AS k ON k.identifier IS e.identifier >> GROUP BY b.identifier >> >> >> Unfortunately, my output is looking much like this: >> >> identifier = homer >> keywords = love; honor; truth; justice; >> entities = jove; troy; helen; son; ship; jove; troy; helen; son; ship; >> jove; troy; helen; son; ship; jove; troy; helen; son; ship; jove; troy; >> helen; son; ship; jove; troy; helen; son; ship; >> >> >> What am I doing wrong? >> >> >> -- >> Eric Morgan >>
