SQL中的列不是一对多关系中的一对一值

I am trying to write a mysql query to select all rows of a table where the one to one relation column is 0 of a one to many relation. basicaly i want all leads where finished is not 1.

我正在尝试编写一个mysql查询来选择表的所有行,其中一对一关系列是一对多关系的0。基本上,我希望所有已完成的线索不是1。

My failing query

我的查询失败了

SELECT * from `leads`
LEFT JOIN `call_lead` on `leads`.`id` = `call_lead`.`lead_id` 
LEFT JOIN `call_result_codes` ON `call_lead`.`call_result_code_id` = `call_result_codes`.`id` 
AND `call_result_codes`.`finished` in (0) group by `leads`.`id`

This fails and still returns all leads even if they have a code with finished 1.

这失败并仍然返回所有潜在客户,即使他们的代码已完成1。

Expected output would be

预期的产出将是

lead with id 12 and lead with id 2.

id为12的导联和id为2的导联。

Table leads

_____________________
|id  | name          |
|2   | test name     |                
|8   | test name2    |
|12  | test name2    |

Table call_lead

_____________________________________________________
|id | lead_id | user_id | call_result_code_id |remark|
|22 | 8       | 1       | 0                   |test  |
|23 | 8       | 1       | 1                   |test  |
|24 | 2       | 1       | 0                   |test  |

Table call_result_codes

________________________________
|id | description   | finished | 
|0  | not answering | 0        |                 
|1  | not interested| 1        |

2 个解决方案

#1

You can use EXISTS() :

您可以使用EXISTS():

SELECT * FROM `leads`
LEFT OUTER JOIN `call_lead` on `leads`.`id` = `call_lead`.`lead_id` 
WHERE NOT EXISTS(SELECT 1 FROM `call_result_codes`
                 WHERE `call_lead`.`call_result_code_id` = `call_result_codes`.`id` 
                   AND `call_result_codes`.`finished` = 1)

I assumed by your expected output that you want those who doesn't have a record in call_results_codes too, so its enough that they don't have a record there with finished = 1.

我假设你的预期输出你想要那些在call_results_codes中没有记录的人,所以它足以让他们没有一个记录,其中finish = 1。

#2

try this. You have the finish column in the join condition. i put in where to find the result

尝试这个。您在连接条件中有完成列。我放在哪里找到结果

SELECT * from `leads`
LEFT JOIN `call_lead` on `leads`.`id` = `call_lead`.`lead_id` 
LEFT JOIN `call_result_codes` ON `call_lead`.`call_result_code_id` = `call_result_codes`.`id` 
WHERE `call_result_codes`.`finished` = 0
group by `leads`.`id`

#1