On Oct 29, 2021, at 9:46 AM, Eric Lease Morgan <[log in to unmask]> wrote:
> 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
> );
Thank you for all the useful replies! After plenty o' reading and plenty o' experimentation, I've decided the use of DISTINCT is the quickest and most immediately practical way for me to remove duplicates, like this:
SELECT b.identifier,
b.title,
GROUP_CONCAT( DISTINCT( e.entity ) ) AS entities,
GROUP_CONCAT( DISTINCT( 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 = e.identifier
GROUP BY b.identifier;
The result is not beautiful but more than functional. For example, here are a few results from a few different records:
identifier = author-homer-gutenberg
title = The Works of Homer
entities = achilles,apollo,hector,ilius,jove,menelaus,minerva,son of atreus,troy,ulysses
keywords = achaeans,achilles,hector,jove,menelaus,telemachus,trojans,ulysses
identifier = title-AustenSense_1811-gutenberg
title = Sense and Sensibility by Jane Austen
entities = barton,cleveland,dashwood,dear ma'am,dear, dear elinor,delaford,devonshire,edward,jennings,john willoughby,london,lucy,marianne,marlborough,middletons,no, elinor,norland,now, edward,selfish,willoughby
keywords = dashwood,edward,elinor,jennings,lucy,marianne,palmer,willoughby
identifier = crawl-planet20210207-code4lib
title = A crawl of Planet Code4Lib (October 7, 2021)
entities = detroit,donald trump,function,google,leaflet,madoff,rails autoscale,tether,the readme project,topics,trump,twitter,u.s.,us,windsor
keywords = data,digital,google,university
While I know the use of a sub SELECT would produce more expressive results, I could never get it to work. Too complicated; the use of DISTINCT is more elegant.
Fun with SQL.
--
Eric Morgan
