MySQL 问题 : Inner Join with Subquery returning no results

Question

我有一个数据库，其中包含一组具有不同表单版本的表单。下面的“工作”查询成功地根据每个表单版本的批准使用日期选择了最新的表单版本。

我想将此查询“工作”查询与另一个名为“forms_types”的表连接起来，该表返回“form_type”列的完整文本描述。

我的尝试没有成功 - 我应该在查询的“连接”版本上获得 50 个结果，但它没有返回任何结果。

如果我能提供任何帮助或见解来让我的工作查询正确加入，我们将不胜感激!

正在工作(50 个结果，但没有 JOIN):

SELECT *
FROM forms AS t
WHERE california =
    ( 
    SELECT MAX(california)
    FROM forms
    WHERE `form_number` = t.`form_number`
    AND trucking_coverage= 1
    AND california IS NOT NULL
    AND california <= '2014-01-29'
    AND form_type <> 0
    )
ORDER BY `t`.`form_type` ASC, `t`.`form_number` ASC

不工作(没有结果):

SELECT *
FROM forms AS t
INNER JOIN forms_types 
ON 'forms.form_type' = 'forms_types.form_type'
WHERE california =
    ( 
    SELECT MAX(california)
    FROM forms
    WHERE `form_number` = t.`form_number`
    AND trucking_coverage= 1
    AND california IS NOT NULL
    AND california <= '2014-01-29'
    AND form_type <> 0
    )
ORDER BY `t`.`form_type` ASC, `t`.`form_number` ASC

Answer 1

您正在寻找这样的东西吗？

SELECT f.*, t.*
  FROM 
( 
  SELECT form_number, MAX(california) california
    FROM forms
   WHERE trucking_coverage = 1
     AND california IS NOT NULL
     AND california <= '2014-01-29'
     AND form_type <> 0
   GROUP BY form_number
) q JOIN forms f
    ON q.form_number = f.form_number
   AND q.california = f.california JOIN forms_types t
    ON f.form_type = t.form_type
 ORDER BY f.form_type, f.form_number

它也可以用这种方式重写(通过利用非标准 MySQL GROUP BY 扩展)

SELECT *
  FROM
(
  SELECT *
    FROM forms f JOIN forms_types t
      ON f.form_type = t.form_type
   WHERE f.trucking_coverage = 1
     AND f.california IS NOT NULL
     AND f.california <= '2014-01-29'
     AND f.form_type <> 0
   ORDER BY california DESC
) q
 GROUP BY f.form_number
 ORDER BY f.form_type, f.form_number