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 <[log in to unmask]> 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 <[log in to unmask]> 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
>>