Print

---

I don't know if this will do exactly what you want at the edges, but you 
can use subqueries in a straightforward way:


SELECT  es.identifier, es.entities, ks.keywords
FROM
(SELECT b.identifier AS identifier,
          GROUP_CONCAT( e.entity,  '; ' ) AS entities
   FROM bibliographics AS b
   LEFT JOIN entities  AS e ON e.identifier IS b.identifier
   GROUP BY b.identifier) AS es
   JOIN (
   SELECT b.identifier as identifier,
          GROUP_CONCAT( k.keyword, '; ' ) AS keywords
   FROM bibliographics AS b
   LEFT JOIN keywords  AS k ON k.identifier IS b.identifier
   GROUP BY b.identifier) AS ks on es.identifier = ks.identifier;

On 10/29/21 9:46 AM, Eric Lease Morgan 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