Hi Eric,

Just join both entities and keywords to bibliographies:

  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 = b.identifier
  LEFT JOIN keywords  AS k ON k.identifier = b.identifier
  GROUP BY b.identifier

HTH,

--
Mike Rylander
Research and Development Manager
Equinox Open Library Initiative
1-877-OPEN-ILS (673-6457)
work: [log in to unmask]
personal: [log in to unmask]
https://equinoxOLI.org

On Fri, Oct 29, 2021 at 9:47 AM 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