mysql - 使用两个表上的 where 子句使用 mysql left join 获取数据

Question

我有 2 张桌子，custom_leads_fields并且custom_leads_fields_option. 的在表中primary key保存custom_leads_fields为。custom_leads_fields_optionc_id

我需要从custom_leads_fields表 where获取所有记录status = 1，然后我还需要从custom_leads_fields_optionwhere匹配记录status = 1

custom_leads_fields table
c_id | user_id | label | status | 
1    | 591     |   A   |   1    |
2    | 591     |   B   |   1    |
3    | 591     |   C   |   0    |

custom_leads_fields_option table
id | c_id                   | option | status 
1  |    2                   |   yes  |   1
2  |    2                   |   no   |   1
3  |    2                   |   may  |   0
4  |    3                   |   yy   |   1
5  |    3                   |   zz   |   1

需要输出：

c_id   |  label  |   option
1      |    A    |     
2      |    B    |     yes
2      |    B    |     no

它应该return records从first table if status = 1即使records are not available in second table，但如果records are available在second table那么only those records should be fetched谁status = 1。

我写了一个查询，但它没有返回标签“A”，因为匹配的记录在其他表中不可用。

SELECT 
`custom_leads_fields`.`c_id` AS `field_id`, 
`custom_leads_fields_option`.`id` AS `option_id`, 
`custom_leads_fields`.`label`, 
`custom_leads_fields_option`.`option` 
FROM 
`custom_leads_fields` 
LEFT JOIN 
`custom_leads_fields_option` 
ON custom_leads_fields.id = custom_leads_fields_option.custom_leads_field_id 
WHERE 
(`custom_leads_fields`.`user_id`=591) 
AND (`custom_leads_fields`.`status`=1) 
AND (`custom_leads_fields_option`.`status`= 1) 

标签： mysqlsql

Answer 1

custom_leads_fields_option我认为是左连接，标签 A 与表没有任何关系。所以你不能使用custom_leads_fields_option.status = 1. 你可以试试这个

select clf.*, clfo.*
from custom_leads_fields clf
left join custom_leads_fields_option clfo
on clf.id = clfo.c_id
where clf.status = 1
and (clfo.status = 1 or clfo.status is null);

sqlfiddle