mysql 连接具有特殊数据的表

Question

我有两张 table

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
107   2046     A8             135   22505    2046    A7        0
108   2046     A9             136   22505    2046    A8        0
109   2046     A10            137   22505    2046    A9        0
---------------------         138   22505    2046    A10       0 

我需要将表格连接成这种格式:

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    A7       0
107   2046    A8         22505    A8       0
108   2046    A9         22505    A9       0
109   2046    A10        22505    A10      0

我的代码如下

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 >= 0 AND 
t2.w_id IN (22404, 22505) 
GROUP BY t1.id

可能是我的数据库有冲突，导致结果不是全部正确，A7-A10行都是错误的。

我的结果是:

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
106   2046    A8         22505    A1       1
106   2046    A9         22505    A1       1
106   2046    A10        22505    A1       1

谁能教我如何解决并给我建议？

Answer 1

尝试以下:

SELECT t1.id,t1.q_id,t1.content,t2.w_id,t2.c_id,t2.ranking
FROM table_1 t1
JOIN table_2 t2 ON t2.c_id = t1.id OR t2.c_id = t1.content
WHERE t1.q_id = 2046 AND 
t2.q_id = 2046 AND 
t2.ranking >= 0 AND 
t2.w_id IN (22404, 22505) 
GROUP BY t1.id