[CODE4LIB] sql join query

[Eric Lease Morgan]

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