MySQL查询语法 - 未知列的错误，但列中存在

Question

有什么不对这个查询：

SELECT * 
FROM families f JOIN family_histories fh ON f.id = fh.family_id 
WHERE f.family_status_cat_id = 1422 
AND (SELECT hst.created_at 
    FROM family_histories hst 
    WHERE hst.family_history_cat_id = 1422 
    AND hst.new_value LIKE '%1422%') 
BETWEEN '2010/2/13' AND '2013/3/6' 

我得到 -

未知列 'hst.new_value' 在 'where子句'

。 但列存在！

Answer 1

尝试此查询 -

SELECT * FROM families f 
    JOIN family_histories fh 
    ON f.id = fh.family_id 
WHERE 
    f.family_status_cat_id = 1422 AND 
    fh.created_at BETWEEN '2010-02-13' AND '2013-03-06' AND 
    fh.family_history_cat_id = 1422 AND 
    fh.new_value LIKE '%1422%' 

这是你想要的吗？

Answer 2

如果列名是new_val，那么你应该有下面的查询

SELECT * FROM families f 
JOIN family_histories fh 
ON f.id = fh.family_id 
WHERE 
f.family_status_cat_id = 1422 AND 
fh.created_at BETWEEN '2010-02-13' AND '2013-03-06' AND 
fh.family_history_cat_id = 1422 AND 
fh.new_val LIKE '%1422%' 

你不会需要第二个选择，你在查询中有当前。它与Devart提到的查询相同。