title: SQLite Join Table from Subquery
body: <p>I have the following scheme in a SQLite table (TABLE 1):</p>
  
  <pre><code>ID          NAME
  ---------------------------------
  1           "NAME 1"
  2           "NAME 2"
  ...         ...
  </code></pre>
  
  <p>The Id from the table is a foreign key in another table like this one (TABLE 2):</p>
  
  <pre><code>ID           NAME            DEPARTMENT (FROM TABLE 1)
  ------------------------------------------------------------
  1            "PRODUCT 1"     2
  2            "PRODUCT 2"     2
  3            "PRODUCT 3"     1
  </code></pre>
  
  <p>I need to return a query with all of the elements in the TABLE 1 and with a new column named "PRODUCTS" which is the count of all the entries in Table 2 where the Department = Table1.ID.</p>
  
  <p>The result would look like this</p>
  
  <pre><code>ID         NAME          ENTRIES
  ---------------------------------------------
  1          "NAME 1"      1 (SELECT COUNT FROM TABLE 2 WHERE DEPARTMENT = 1)
  2          "NAME 2"      2 (SELECT COUNT FROM TABLE 2 WHERE DEPARTMENT = 2)
  </code></pre>
  
  <p>I don't know how to do this in SQLite. Is it possible? How could the query would have to be written?</p>