Re: [CODE4LIB] sql join query

Howdy Eric, My SQL is awful, but my first thought was a DISTINCT inside the GROUP_CONCAT. A quick google turned this up: https://stackoverflow.com/questions/3083499/mysql-distinct-on-a-group-concat Maybe it gets you closer? Yours, Kevin On 10/29/21 10:02, Eric Lease Morgan wrote: On Oct

Re: [CODE4LIB] sql join query

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

Re: [CODE4LIB] sql join query

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

Re: [CODE4LIB] sql join query

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 J

Re: [CODE4LIB] sql join query

Hi, Eric: is this page useful for you? https://www.sqlitetutorial.net/sqlite-union/ SB > On 29 Oct 2021, at 15:46, Eric Lease Morgan wrote: > > How do I join multiple tables in an SQLite database in order to output the > values in the linked tab

Re: [CODE4LIB] sql join query

On Oct 29, 2021, at 9:54 AM, Mike Rylander wrote: > 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

Re: [CODE4LIB] sql join query

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 keywo

[CODE4LIB] sql join query

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 TEX