SQL Server - Problema con consulta condicional en varias tabla

Hola a todos, tal vez el título no se ajusta a mi problema pero bueno... :)

El asunto es q tengo esta consulta:

Select a.fecha_Desp, a.hora_desp, a.origen, a.destino, a.cod_desp, a.obs_desp, b.des_despacho
from DocxDespacho as a, Despacho as b
where a.cod_doc = 33 and a.cod_desp = b.cod_despacho

para poder cambiar el codigo de despacho q estaba en número (cod_desp) a su respectiva descripción (des_Despacho). Hasta ahi todo bien, el problema viene con los campos origen y destino, tales campos se muestran en código (DXX si es de la tabla Dependencia y SDXX si es de la tabla Subdependencia) y lo q busco es mostrar las descripciones de las mismas. MI problema es xq cuando hago la consulta q puse líneas arriba, me peden salir n resultados y x ejemplo los resultados puede ser(mostrando solo los campos origen y destino):
origen destino
D12 | SD3
SD10 | D3
D10 | D5
SD11 | SD3

Cómo tendría que ser mi consulta para q me salga las descripciones en vez de los códigos de origen y destino?? Muchas gracias de antemano.

Hola,
Espero que sea lo que estás necesitando, o al menos que te sirva de orientación.
Liliana.

create table #test
(origen char(5), destino char(5))
insert #test select 'D12', 'SD3'
insert #test select 'SD10', 'D3'
insert #test select 'D10', 'D5'
insert #test select 'SD11', 'SD3'

create table #Dependencia (cod char(5), nombre char(20))
insert #Dependencia select 'D12', 'Dependencia 12'
insert #Dependencia select 'D3', 'Dependencia 3'
insert #Dependencia select 'D5', 'Dependencia 5'
insert #Dependencia select 'D10', 'Dependencia 10'

create table #SubDependencia (cod char(5), nombre char(20))
insert #SubDependencia select 'SD11', 'SubDependencia 11'
insert #SubDependencia select 'SD3', 'SubDependencia 3'
insert #SubDependencia select 'SD10', 'SubDependencia 10'

select Origen, NombreOrigen = COALESCE(do.nombre, sdo.nombre),
Destino, NombreDestino = COALESCE(dd.nombre, sdd.nombre)
from #test
left join #Dependencia DO ON Origen = do.cod
left join #SubDependencia SDO ON Origen = sdo.cod
left join #Dependencia DD ON Destino = dd.cod
left join #SubDependencia SDD ON Destino = sdd.cod

muchas gracias x responder Lilinana, voy a probar tu consulta.. te cuento como me fue. Gracias nuevamente

hola Liliana, me sirvió mucho tu consulta, era lo q estaba necesitando, muchas gracias. Suerte, bye bye