sql - How to avoid duplicates in creating view with Left Join if a joining column in one table can have duplicates? -

i need create view 2 tables. linked 1 column, in 1 table column primary key, in table column can have duplicates. resulting view should not have duplicates column. if row in 2nd table meets case condition result in view should 'y' regardless on other rows same key.

please, see scrip , join results. the 1st row in results incorrect, should eliminated

create table a1table(     integer not null );  create table a2table(     integer,     b integer );  insert a1table values (1); insert a1table values (2); insert a1table values (3); insert a2table values (1, 1); insert a2table values (1, 100);  create view a12 select a1.a, case     when a2.b=100 , a2.b not null 'y'     else 'n' end report a1table a1 left join a2table a2 on a2.a = a1.a; 

-->

   | report   ---+--------  **1 | n** -> *should eliminated*    1 | y    2 | n    3 | n 

put condition on a2.b in join condition.

select a1.a,        if(a2.b not null, "y", "n") report a1table a1 left join a2table a2 on a1.a = a2.a , a2.b = 100 

i'm assuming combination a, b should unique in a2table. if can have duplicate combinations, need use subquery distinct:

select a1.a,        if(a2.b not null, "y", "n") report a1table a1 left join (select distinct a, b            a2table            b = 100) a2 on a1.a = a2.a