SQL IS NULL 检查未给出正确结果

debugcn 发表于 Dev

哈比布拉

我有三张表：mm_members, cmn_addresses,mm_member_statement_delivery_options

在mm_members, 我有列 -> member_id, member_number,member_name

在cmn_addresses, 我有列 -> reference_id, reference_type, email,address和这里reference_id是一样的member_id

在mm_member_statement_delivery_options, 我有列 -> member_id,statement_delivery_method

我想要所有member_numbers拥有mm_member_statement_delivery_options,statement_delivery_method IN(2,3)并且cmn_addresses他们没有电子邮件并且有 reference_type = 3

我生成了下面的查询，这没有得到想要的结果

SELECT member_number FROM mm_members a 
JOIN mm_member_statement_delivery_options b ON a.member_id=b.member_id 
JOIN cmn_addresses c ON b.member_id=c.reference_id 
WHERE c.reference_type = 3
AND b.statement_delivery_method IN (2,3)
AND  (c.EMAIL IS NULL OR c.EMAIL = "");

玛吉

Hope it will work for you :)

SELECT 
DISTINCT mm_members.member_number AS MEMBER_NUMBER, 
TRIM(CONCAT(mm_members.first_name,' ',mm_members.last_Name)) AS MEMBER_NAME, 
cmn_addresses.email AS MEMBER_EMAIL, 
mm_member_statement_delivery_options.statement_delivery_method 
FROM mm_members 
JOIN cmn_addresses ON cmn_addresses.reference_id = mm_members.member_id AND cmn_addresses.reference_type = 3 
JOIN mm_member_statement_delivery_options ON mm_member_statement_delivery_options.member_id = mm_members.member_id 
WHERE (cmn_addresses.email IS NULL OR cmn_addresses.email = '') 
AND mm_member_statement_delivery_options.statement_delivery_method IN ('2','3') 
ORDER BY MEMBER_NUMBER;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。