title: SQL: Retrieving data from two tables via a 3rd linking key table
body: <p>I have 3 tables as follows:</p>
  
  <pre><code>CREATE TABLE "families" (
      "id_family" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
      "Family" TEXT
  );
  CREATE TABLE "levels" (
      "id_levels" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
      "Level" TEXT UNIQUE,
      "Description" TEXT
  );
  CREATE TABLE "level_families" (
      "id_levels" INTEGER REFERENCES "levels" ("id_levels"),
      "id_family" INTEGER REFERENCES "families" ("id_family")
  );
  
  INSERT INTO "families" ( "id_Family","Family" ) VALUES ( '1','Work' );
  INSERT INTO "families" ( "id_Family","Family" ) VALUES ( '2','Hobbies' );
  Insert Into "levels" ("id_levels", "level") Values ('1', 'Beginner');
  Insert Into "levels" ("id_levels", "level") Values ('2', 'Elementary');
  Insert Into "level_families" ("id_levels", "id_family") Values ('1', '1');
  Insert Into "level_families" ("id_levels", "id_family") Values ('1', '2');
  Insert Into "level_families" ("id_levels", "id_family") Values ('2', '2');
  </code></pre>
  
  <p>I'm trying to retrieve all levels with their families concatenated, example output:</p>
  
  <p>Beginner: Work, Hobbies</p>
  
  <p>Elementary: Hobbies</p>
  
  <p>Using the following code:</p>
  
  <pre><code>SELECT t.level, f.family
  FROM families f
  JOIN (
          SELECT lf.id_family, l.level
          FROM  level_families lf
                  JOIN levels l
                  ON lf.id_levels=l.id_levels
      ) t
  ON f.id_family=t.id_family;
  </code></pre>
  
  <p>I'm getting:</p>
  
  <p>Beginner: Work</p>
  
  <p>Beginner: Hobbies</p>
  
  <p>Elementary: Hobbies</p>