If you know that the keywords and entities don't have any commas in them
then the following is an alternative to subqueries:
SELECT b.identifier,
replace(GROUP_CONCAT(distinct e.entity), ',', '; ') AS entities,
replace(GROUP_CONCAT( distinct 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
The caveat is necessary because distinct can only be used in this way in
a one argument function, so you have to accept the default way that
group_concat joins the values and fix it up.
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
|