sql server - SQL Joining tables with 'constants' -

i have table of orders,

invoice   location    customer code   salespersonemail ------------------------------------------------------ 300001    001         cus001          ? 300002    006         cus002          ? 

and table of email groups,

role              email  ----------------------------------------------------- filtered_group    management@gmail.com;john@gmail.com 

when location = 001, salespersonemail must email field filtered_group

salespersonemail other locations must "orders@gmail.com;" + email role no_filter_group.

i'm using following achieve this,

select i.invoice, i.location, i.[customer code],      case when i.location = 001           f.email           else n'orders@gmail.com;' + nf.email salespersonemail     end   invoice i, rolecodes f, rolecodes nf  f.role = n'filtered_group' , nf.role = n'no_filter_group' 

my problem role no_filter_group may not exist in role table @ times, causes above query return nothing.

how join these tables if no_filter_group not exist in table, rows have salespersonemail of filtered_group still returned query?

thanks

a relatively simple way use left join , put special number 001 location , special role names filtered_group , no_filter_group in join condition.

in sql fiddle can comment/uncomment 1 line in schema definition see how works when rolecodes has row no_filter_group , when doesn't.

in case, query return rows invoice table.

select   invoice.invoice   ,invoice.location   ,invoice.[customer code]   ,case when invoice.location = '001'   rolecodes.email   else 'orders@gmail.com;' + isnull(rolecodes.email, '')   end salespersonemail   invoice   left join rolecodes on     (invoice.location = '001'      , rolecodes.role = 'filtered_group')     or     (invoice.location <> '001'      , rolecodes.role = 'no_filter_group')