title: SQLite many to many join and group
body: <p>This is a very normal setup for many-to-many relational tables.</p>
  
  <ol>
  <li><code>product</code> list and <code>category</code> list.</li>
  <li>Each <code>product</code> belongs to <code>(0,n)</code> <code>category</code>.</li>
  <li>Each <code>category</code> contains <code>(0,n)</code> <code>product</code>.</li>
  </ol>
  
  <h2>TABLES</h2>
  
  <pre><code>CREATE TABLE product (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL);
  
  CREATE TABLE category (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL);
  
  CREATE TABLE product_category (
      product_id INTEGER NOT NULL,
      category_id INTEGER NOT NULL);
  </code></pre>
  
  <h2>PRODUCT table</h2>
  
  <pre><code>| id | name |
  =============
  | 1  |  p1  |
  | 2  |  p2  |
  | 3  |  p3  |
  </code></pre>
  
  <h2>CATEGORY table</h2>
  
  <pre><code>| id | name |
  =============
  | 1  |  c1  |
  | 2  |  c2  |
  | 3  |  c3  |
  </code></pre>
  
  <h2>PRODUCT_CATEGORY table</h2>
  
  <pre><code>| product_id | category_id |
  ============================
  |     1      |      1      |
  |     1      |      2      |
  |     1      |      3      |
  |     2      |      1      |
  |     2      |      2      |
  |     3      |      1      |
  </code></pre>
  
  <h2>Question</h2>
  
  <p>I would like to know how to query for <code>product</code> by <code>category_id</code> and have the <code>product</code> listed with all <code>category_id</code> that it is in.</p>
  
  <p>For example, I want to query the list of <strong>products</strong> that are available in <strong>category = 1</strong>, and the result must contain a <code>categories_id</code> where all categories that the <code>product</code> belongs to must be listed.</p>
  
  <h2>Expected output</h2>
  
  <pre><code>| product_id | product_name | categories_id |
  =============================================
  |     1      |      p1      |     1,2,3     |
  |     2      |      p2      |     1,2       |
  |     3      |      p3      |     1         |
  </code></pre>
  
  <h2>Attempts</h2>
  
  <p>I know this can be done with sub query, but I would like to know whether this can be achieved by <code>JOIN</code> statement?</p>
  
  <p>I have tried the following query but the results does not reflect what I want.</p>
  
  <h2>JOIN QUERY statement</h2>
  
  <pre><code>SELECT
      P.id AS product_id,
      P.name AS product_name,
      GROUP_CONCAT(PC.category_id, ',') AS categories_id
          FROM product AS P
              LEFT JOIN product_category AS PC
                  ON P.id = PC.product_id
          WHERE PC.category_id = 1
          GROUP BY P.id;
  </code></pre>
  
  <h2>Output (not as intended)</h2>
  
  <pre><code>| product_id | product_name | categories_id |
  =============================================
  |     1      |      p1      |     1         |
  |     2      |      p2      |     1         |
  |     3      |      p3      |     1         |
  </code></pre>
  
  <p>Can anyone advice on how this can be achieved?</p>