SQL server 2005 - 3 table conundrum -

sql server 2005 running under 2000 compatibility mode.

i have 3 tables: banks, exceptions , clients; client allowed trade bank except associated him on exceptions table. below "schema":

banks        exceptions        clients -----        ----------        ------- bkid         bkid                            clid              clid 

my question is: how can find clients have maximum 2 banks they're allowed trade (i.e. not on exceptions table), , 1 of 2 banks being specific one, same clients.

an alternate way of rephrasing question be: how can find clients have 1 specific bank in common (we can choose jpm example) , @ 1 other bank, they're allowed trade?

so far i've created function counts how many banks available given clid, can't figure out how add condition of clients having @ least jpm in common...

thanks all!

ps: function code

alter function [dbo].[fngetnbavailablebanks](@clientid varchar(10)) returns integer begin  declare @intreturn integer  set @intreturn = (select                          count(*) numbanks                                              banks fxb                         left outer join exceptions bx                             on bx.clid= @clientid , fxb.bkid = bx.bkid                                             bx.bkid null                         , isnull(fxb.bobsolete, 0) = 0)  return @intreturn  end 

the query i'm trying run far:

select *      (select         clid,         dbo.fngetnbavailablebanks(clid) cnt             clients) t t.cnt <= 2 

as can see return clients <= 2 available banks); need further filter them ones contain jpm among 2 available banks , i'm asking i'm looking elegant solution.

this should work:

select clid (select distinct t1.bkid, t2.clid clients t1, banks t2      t2.bkid not in       (select  bkid  exceptions t3 t1.clid = t3.clid ) ) t1                            clid  not in (select clid exceptions bkid = %yourspecific bankid%)   group clid   having count(*) <= 2 

you can use hash tables instead select statment in brackets make query work faster.