title: mysql joining tables for multiple times
body: <p>I have two tables</p>
  
  <pre><code>table 1                             table 2
  id   q_id   content                 id    w_id     q_id    c_id    ranking
  ----------------------          ------------------------------------------------
  95   2046   1=E                     123   22404    2046    100     0
  96   2046   2=G                     124   22404    2046    101     1
  97   2046   3=N                     125   22404    2046    102     1
  98   2046   4=B                     126   22404    2046    103     1
  99   2046   5=V                     127   22404    2046    104     2
  100  2046   A1                      128   22404    2046    105     2
  101  2046   A2                      129   22505    2046    A1      0
  102  2046   A3                      130   22505    2046    A2      2
  103  2046   A4                      131   22505    2046    A3      1
  104  2046   A5                      132   22505    2046    A4      2
  105  2046   A6                      133   22505    2046    A5      3
  106  2046   A7                      134   22505    2046    A6      3
  -----------------------             135   22505    2046    A7      0
  </code></pre>
  
  <p>I want to execut to this format(sd didn't calculate here)</p>
  
  <pre><code>id     content     av     total     E     G     N     B     V     sd
  ----------------------------------------------------------------------
  100      A1        1        2       2     0     0     0     0      0
  101      A2       1.5       2       0     1     1     0     0      0
  102      A3        1        2       1     1     0     0     0      0
  103      A4        2        2       0     2     0     0     0      0
  104      A5        3        2       0     0     2     0     0      0
  105      A6        3        2       0     0     2     0     0      0
  106      A7        3        1       0     0     1     0     0      0
  </code></pre>
  
  <p>The Code modified base on Mark's suggestion: </p>
  
  <pre><code>SELECT *
  FROM table_1 t1
  JOIN table_2 t2 ON t2.c_id in (t1.id, t1.content)
  WHERE t1.q_id = 2046 AND 
    t2.q_id = 2046 AND 
    t2.ranking &gt;= 0 AND 
    t2.w_id IN (22404, 22505) 
  GROUP BY t1.id
  </code></pre>
  
  <p>Result: </p>
  
  <pre><code>id    q_id    content    w_id    c_id    ranking
  -------------------------------------------------------
  100   2046    A1         22404    100      0
  101   2046    A2         22404    101      1
  102   2046    A3         22404    102      1
  103   2046    A4         22404    103      1
  104   2046    A5         22404    104      2
  105   2046    A6         22404    105      2
  106   2046    A7         22505    A1       1
  </code></pre>
  
  <p>The c_id of row 106 should be A7 instead of A1, and the ranking is also wrong. As I have 20rows (A1-A20). The rows of A7-A20 are wrong. All the c_id and ranking of A7-A20 became A1 and 1. </p>