mysql left join and not equals should return only one row

Question

Can some one please help me with the query for below requirement.

for above two tables i wrote below left join query

select distinct result 
from a 
    left join b on a.number = b.reference 
where a.color='red' and b.value != '10'

this query is returning both 1 and 2 as output.

but i am expecting only 2 as the output since list1 in table b have value:10 so query should not return row if value in list matches.

Answer 1

You can do this with a left join. But the filtering condition is mostly in the on clause:

select a.result 
from a left join
     b
     on a.number = b.reference and b.value = '10'
where a.color='red' and b.reference is null;