sql server - How to use field value of joined table for join rather than foreign key -

i have new database schema need query , having trouble getting data need without using primary key in join. didn't design schema have work it. tried creating sqlfiddle kept giving me gateway error when tried.

basically have contacts table contains contacts stored in database. contacts table contains 2 fields reference tables contact linked (vendor , client). contact table contains field named "contacttype". links table defines type of contact (owner or employee).

[client] clientid | name ============================== 1        | uptown dining 2        | downtown eatery 3        | midtown steakhouse  [vendor] vendorid | name ============================== 1        | abc produce 2        | def seafood 3        | ghi meats  [contacttype] contacttypeid | name ============================== 1             | owner 2             | employee  [contact] contactid | tablename | tableid | contacttypeid | firstname | lastname ========================================================================     1     | client    |    1    |       1       | bob       | smith     2     | vendor    |    1    |       1       | jill      | weston     3     | vendor    |    1    |       2       | alice     | jenkins     4     | client    |    2    |       1       | chris     | brown     5     | vendor    |    3    |       2       | todd      | davis 

what trying list of client contacts owners of company. sql works that.

select    contact.firstname,   contact.lastname client left join contact   on client.clientid = contact.tableid    , contact.tablename = 'client'    , contact.contacttypeid = 1 

this give me ..

bob smith chris brown 

the problem don't want use and contact.contacttypeid = 1 in join. want able use owner or employee in place of primary key (1 or 2) of contacttype table in join unsure of how this. please keep in mind restriction needs in join since need reserve where clause filter specific clients.

for example if wanted query specific vendor , list of owner , employee in same row ...

vendor name | owner first | owner last | employee first | employee last ============================================================================ abc produce |     jill    |    weston  |       alice    |     jenkins 

i'm not sure understand problem correctly, if i'm reading right - want have chance specify "owner" or "employee" explicitly in where clause rather 1 or 2.

perhaps looking for:

select    contact.firstname,   contact.lastname client left join contact   on client.clientid = contact.tableid    , contact.tablename = 'client'  left join contacttype ct on ct.contacttypeid = contact.contacttypeid -- can use directly, below ct.name = 'owner' 

edit: response comment

not without getting messy... can use derived inline table, or view, of these performance killers. least performance-impairing (but still ugly) way can think of this:

select    contact.firstname,   contact.lastname client left join contact   on client.clientid = contact.tableid    , contact.tablename = 'client'    , contact.contacttypeid = (select iix.contacttypeid contacttype iix iix.name = 'owner')