combine two id into a new table?
<p>i had a task about text processing and i don't know how to combine some columns from separate tables into one table</p>

<p>so here is the case:
i have a table named <code>list</code> with <code>id_doc</code>, and <code>title</code> columns
then i create a new table named <code>term_list</code> which contains a list of result terms when i do some text processing to titles from <code>list</code>.</p>

<p>the <code>term_list</code> table have <code>id_term</code>, <code>term</code>, <code>df</code>, and <code>idf</code> column. Lastly, i want to have a table named <code>term_freq</code> which has columns <code>id</code>, <code>id_term</code>, <code>id_doc</code>, <code>tf</code>, and <code>normalized_tf</code></p>

<p>example :
table <code>list</code> is like this:</p>

<pre><code>id_doc             titles
  11      information retrieval system
  12          operating system
  13         business information
</code></pre>

<p>table <code>term_list</code> is below this:</p>

<pre><code>id_term     term         df    idf
   21     information     2     --
   22      retrieval      1     --
   23       system        2     --
   24      operating      1     --
   25       business      1     --
</code></pre>

<p>I want to ask how to create a table <code>term_freq</code> so that the table becomes like this?</p>

<pre><code>id    id_term     id_doc    tf      normalized_tf
31      21          11       1           -- 
32      22          11       1           --
33      23          11       1           --
34      24          12       1           --
35      23          12       1           --
36      25          13       1           --
37      21          13       1           --
</code></pre>

<p>the main problem is i have to join <code>id_term</code> and <code>id_doc</code> into one table that one <code>id_doc</code> has relation to several <code>id_term</code> but i don't know how to correlate because <code>list</code> and <code>term_list</code>doesn't have any similar column.</p>

<p>Please help :(</p>

<p>You can iterate over rows in <code>term_list</code>:</p>

<pre><code>SELECT id_term, term FROM term_list
</code></pre>

<p>for each <code>term</code> make:</p>

<pre><code>SELECT id_doc FROM list WHERE titles LIKE "term"
</code></pre>

<p>and saves pairs <code>id_term</code> and <code>id_doc</code> in the table <code>term_freq</code>.</p>

python mysql database text-processing information-retrieval