mysql - SQL where column is not value in a one to one of a one to many relation -

i trying write mysql query select rows of table 1 one relation column 0 of 1 many relation. basicaly want leads finished not 1.

my failing query

select * `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`  , `call_result_codes`.`finished` in (0) group `leads`.`id` 

this fails , still returns leads if have code finished 1.

expected output be

lead id 12 , lead 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        |                

you can use exists() :

select * `leads` left outer join `call_lead` on `leads`.`id` = `call_lead`.`lead_id`  not exists(select 1 `call_result_codes`                  `call_lead`.`call_result_code_id` = `call_result_codes`.`id`                     , `call_result_codes`.`finished` = 1) 

i assumed expected output want doesn't have record in call_results_codes too, enough don't have record there finished = 1.