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
