sql - Query to exclude two or more records if they match a single value -

i have database table in multiple customers can assigned multiple types. having trouble formulating query exclude customer records match type. example:

id  customername    type ========================= 111 john smith      tfs-a  111 john smith      pro  111 john smith      rway  222 jane doe        pro  222 jane doe        tfs-a  333 richard smalls  pro  444 bob rhoads      pro  555 jacob jones     tfs-b  555 jacob jones     tfs-a  

what want pull people marked pro not marked tfs. if pro , tfs, exclude them.

any appreciated.

you can 'pro' customers , use not exists clause exclude ones 'tfs':

select distinct id, customername mytable t1 [type] = 'pro' , not exists (select 1                                       mytable t2                                      t1.id = t2.id , [type] 'tfs%') 

sql fiddle demo