sql server - SQL Join from multiple tables from a reference lookup into one view -

im in middle of report creation. came through these table structure wierd , complicated. im not able create view out of it.

i have 2 tables structure

dbobjecttype ------------- typeid, description 

and

dbobassign ---------- id referenceobtype referenceobid targetobtype targetobid 

the table populated :

dbobjecttype table:  +--------+-------------------+ | typeid  description        | +--------+-------------------+ | 1       account            | | 2       tfn                | | 3       vdn                | | 4       skill              | +--------+-------------------+  dbobassign table +----+-------------+---------+---------+--------+ | id  refobtype    refobid  tgtobtype   tgtobid | +----+-------------+---------+---------+--------+ | 1    1        12    2     23      |  => refer tfn table | 2    1        12    3     12      |  => refer vdn table | 3    1        23    4     1       |  => refer skill table | 4    1        23    2     45      |  => refer tfn table | 5    1        23    4     31      |  =>refer skill table +----+-------------+---------+---------+--------+ 

my report need show tfn, vdn , skills account

layout like:

+------+-------+------------+---------+      |account tfn    skill         vdn | +------+-------+------------+---------+ | 12    tfnof12       vdn@@@  | | 23    tfnof23 skill1of23            | | 23        skill2 of 23          | +------+-------+------------+---------+ 

right im able relate 1 table. means im joining account table tfn details.

select accountname,tfn,skill tblaccount acc  left outer join tbldbassign dba on dba.refobid = acc.id left outer join tbldbobject dbo on dbo.description= 'tfn' 

is there way other records skill , vdn tables? kindly provide suggestions.

table structure

vdn: --- vdnid vdndesc  tfn: ---- tfnid tfnowner tfndesc  skill: ------ skillid skilldesc 

your script should (note not tested, not clear table names, different gave in structure , sql example):

select accountname, t.tfndesc, v.vdndesc, s.skilldesc  tblaccount acc   left outer join tbldbassign dba1      on dba1.refobid = acc.id left outer join tbldbobject dbo1      on dbo1.description= 'tfn'     , dba1.tgtobtype =  dbo1.typeid left outer join tfn t     on t.tfnid = dba1.tgtobid  left outer join tbldbassign dba2      on dba2.refobid = acc.id left outer join tbldbobject dbo2      on dbo2.description= 'vdn'     , dba2.tgtobtype =  dbo2.typeid left outer join vdn v     on v.vdnid = dba2.tgtobid  left outer join tbldbassign dba3      on dba3.refobid = acc.id left outer join tbldbobject dbo2      on dbo3.description= 'skill'     , dba3.tgtobtype =  dbo3.typeid left outer join skill s     on s.skillid = dba3.tgtobid