mysql join 没有响应

Question

我有两个表，donaTypes和fullInfo .

fullInfo :

id          funddesc        giftamt        giftdate
001        annual fund       50.00          2010-03-09
223        alumni fund       25.00          2009-03-06
334        capital exp      100.00          2011-09-27
...            ...            ...               ...

donaTypes :

   id           donaType
    1          annual fund
    2          capital exp
    3          alumni fund

我正在尝试匹配 fullInfo.funddesc = donaTypes.donaType 的位置希望插入 donaTypes.id号码输入fullInfo table 。这是我的代码，但我只得到空白响应(没有错误):

SELECT st1.funddesc, st2.donatype
FROM 

(select t1.funddesc
from fullInfo as t1) st1

inner join

(select t2.donatype
from donatTypes as t2) st2

on trim(st1.funddesc) like trim(st2.donaType)
; 

我也尝试过:

SELECT t1.funddesc, t2.donatype 
FROM fullInfo as t1, 
donatTypes as t2
where trim(t1.funddesc) = trim(t2.donatype);

理想情况下，我想要fullInfo看起来像这样:

fullInfo :

id          funddesc        giftamt        giftdate
001             1            50.00          2010-03-09
223             3            25.00          2009-03-06
334             2           100.00          2011-09-27
...            ...            ...               ...

Answer 1

在调试之前保持简单一点。您不需要嵌套查询。而且，LIKE 对于连接来说并不是很好，因为它可能有点混杂。

SELECT fi.funddesc, dt.donaType
  FROM  fullinfo fi
  JOIN donatTypes dt on trim(fi.funddesc) = trim(dt.donaType)

您可能还想在两个表上执行此类操作，只是为了弄清楚连接列中实际有哪些内容。

SELECT COUNT(*), concat('>>>',TRIM(funddesc),'<<<') 
  FROM fullinfo 
 GROUP BY concat('>>>',TRIM(funddesc),'<<<')