sql - More efficient way of doing multiple joins to the same table and a "case when" in the select -

at organization clients can enrolled in multiple programs @ 1 time. have table list of of programs client has been enrolled unique rows in , dates enrolled in program.

using external join can take client name , date table (say table of tests clients have completed) , have return of programs client in on particular date. if client in multiple programs on date duplicates data table each program in on date.

the problem have looking return 1 program "primary program" each client , date if in multiple programs on date. have created hierarchy program should selected primary program , returned.

for example:

1.)inpatient

2.)outpatient clinical

3.)outpatient vocational

4.)outpatient recreational

so if client enrolled in outpatient clinical, outpatient vocational, outpatient recreational @ same time on date return "outpatient clinical" program.

my way of thinking doing join table previous programs multiple times this:

from dbo.testtable testtable  left outer join dbo.previousprograms previousprograms1 on testtable.date = previousprograms1.date , previousprograms1.type = 'inpatient'  left outer join dbo.previousprograms previousprograms2 on testtable.date = previousprograms2.date , previousprograms2.type = 'outpatient clinical'  left outer join dbo.previousprograms previousprograms3 on testtable.date = previousprograms3.date , previousprograms3.type = 'outpatient vocational'  left outer join dbo.previousprograms previousprograms4 on testtable.date = previousprograms4.date , previousprograms4.type = 'outpatient recreational' 

and condition case when in select statement such:

select  case          when previousprograms1.name not null             previousprograms1.name         when previousprograms1.name null , previousprograms2.name not null             previousprograms2.name         when previousprograms1.name null , previousprograms2.name null , previousprograms3.name not null             previousprograms3.name         when previousprograms1.name null , previousprograms2.name null , previousprograms3.name not null , previousprograms4.name not null             previousprograms4.name         else null         end primaryprogram 

the bigger problem in actual table there lot more 4 possible programs , case when select statement , joins cumbersome enough.

is there more efficient way either selects part or join part? or possibly better way together?

i'm using sql server 2008.

you can simplify (replace) case using coalesce() instead:

select   coalesce(previousprograms1.name, previousprograms2.name,     previousprograms3.name, previousprograms4.name) previousprogram 

coalesce() returns first non-null value.

due design, still need joins, easier read if used short aliases, example pp1 instead of previousprograms1 - it's lot less code noise.