sql - combine one to more tables and get the values -

i have created insert query combining 1 more tables , take values single.

table 1:

candidatequalifications:

• id
• candidateid
• degreeid
• specialization

table 2:

ug_list

• id
• ugname

table 3:

pg_list

• id
• pgname

table 4:

docorate_list

• id
• ugname

these tables id called follow table,

candidatedetails

• qualification
• postgraduation
• doctorate

it shows separate degrees in candidatedetails table. when use follow query qualification values. others eliminated. if 3 degrees there means candidateid shows 3 times. 1 time show.

my query is,

select top(50) 'insert candidatequalifications(candidateid,degreeid,specialization) values('+  cast(c.candidateid varchar(50))+',''' +  isnull(cast(coalesce(u.id,p.id,d.id)as varchar(50)),'null')+','+  isnull(''''+c.ugspecification+'''', 'null')+')'    candidatedetails c   left join ug_list u on c.qualification=u.ugname   left join pg_list p on c.postgraduation=p.pgname   left join docorate_list d on c.doctorate=d.doctorate 

result set

insert candidatequalifications(candidateid,degreeid,specialization) values(2,'38,'hotel management')  insert candidatequalifications(candidateid,degreeid,specialization) values(3,'17,'hmct (hotel management& catering technology)') 

please me come out problem?

there fundamental problem in requirement in in table candidatequalifications want have degreeid, want link 3 tables.

with example data:

candidatedetails

+--------------------------------------------------+ ¦ id ¦ qualification  ¦ postgraduation ¦ doctorate ¦ ¦----+----------------+----------------+-----------¦ ¦  1 ¦ qualification1 ¦ postgraduation1¦ doctorate1¦ ¦  2 ¦ qualification2 ¦ postgraduation2¦ doctorate2¦ ¦  3 ¦ qualification3 ¦ postgraduation3¦ doctorate3¦ +----+----------------+----------------+-----------+ 

ug_list

+---------------------+ ¦ id ¦      ugname    ¦ ¦----+----------------¦ ¦  1 ¦ qualification1 ¦  ¦  2 ¦ qualification2 ¦  ¦  3 ¦ qualification3 ¦  +----+----------------+ 

pg_list

+---------------------+ ¦ id ¦      pgname    ¦ ¦----+----------------¦ ¦  1 ¦ postgraduation1¦  ¦  2 ¦ postgraduation2¦ ¦  3 ¦ postgraduation3¦  +----+----------------+ 

docorate_list

+-----------------+ ¦ id ¦  dcname    ¦ ¦----+------------¦ ¦  1 ¦ doctorate1 ¦  ¦  2 ¦ doctorate2 ¦ ¦  3 ¦ doctorate3 ¦  +----+------------+ 

what appears looking end in candidatequalifications

+-----------------------------------------------+ ¦ id ¦ candidateid  ¦ degreeid ¦ specialization ¦ ¦----+--------------+----------+----------------¦ ¦  1 ¦      1       ¦     1    ¦      spec1     ¦  -- qualification1 ¦  2 ¦      1       ¦     1    ¦      spec1     ¦  -- postgraduation1 ¦  3 ¦      1       ¦     1    ¦      spec1     ¦  -- doctorate1 ¦  4 ¦      2       ¦     2    ¦      spec2     ¦  -- qualification2 ¦  5 ¦      2       ¦     2    ¦      spec2     ¦  -- postgraduation2 ¦  6 ¦      2       ¦     2    ¦      spec2     ¦  -- doctorate2 ¦  7 ¦      3       ¦     3    ¦      spec3     ¦  -- qualification3 ¦  8 ¦      3       ¦     3    ¦      spec3     ¦  -- postgraduation3 ¦  9 ¦      3       ¦     3    ¦      spec3     ¦  -- doctorate3 +----+--------------+----------+----------------+ 

the trouble because have id's 3 different tables, once have inserted data have no way of knowing 1 links to.

if not late suggest changing data structure follows:

qualificationtype

+----+---------------+ ¦ id ¦      name     ¦ ¦----+---------------¦ ¦  1 ¦ undergraduate ¦  ¦  2 ¦  postgraduate ¦ ¦  3 ¦   doctorate   ¦  +----+---------------+ 

degree

+----+---------------------+-----------------+ ¦ id ¦ qualificationtypeid ¦       name      ¦ ¦----+---------------------¦-----------------¦ ¦  1 ¦        1            ¦  qualifacation1 ¦ ¦  2 ¦        1            ¦  qualifacation2 ¦ ¦  3 ¦        1            ¦  qualifacation3 ¦ ¦  4 ¦        2            ¦ postgraduation1 ¦ ¦  5 ¦        2            ¦ postgraduation2 ¦ ¦  6 ¦        2            ¦ postgraduation3 ¦ ¦  7 ¦        3            ¦    doctorate1   ¦ ¦  8 ¦        3            ¦    doctorate2   ¦ ¦  9 ¦        3            ¦    doctorate3   ¦ +----+---------------------+-----------------+ 

then candidatequalifation table can reference qualifationid, know references.

so insert query becomes (i not sure specialisation comes from):

insert candidatequalifation (candidateid, degreeid) select  cd.candidateid,         dg.degreeid    candidatedetails cd         left join degree ug             on cd.qualification = ug.name             , cd.qualificationtypeid = 1         left join degree pg             on cd.postgraduation = pg.name             , cd.qualificationtypeid = 2         left join degree doc             on cd.doctorate = doc.name             , cd.qualificationtypeid = 3         outer apply         (   values                 (ug.id),                 (pg.id),                 (doc.id)         ) dg (degreeid); 

and can qualifations using like

select  cd.candidateid,         qualification = degree.name,         qualificationtype = qt.name    candidatedetails cd         inner join candidatequalifications cq             on cd.id = cq.candidateid         inner join degree             on degree.id = cq.degreeid         inner join qualificationtype qt             on qt.id = degree.qualificationtypeid; 

however, answer actual question, can separate qualifations sepearate rows using outer apply:

select  cd.candidateid,         d.degreeid    candidatedetails cd         left join ug_list             on cd.qualification = ug_list.ugname         left join pg_list p             on cd.postgraduation = pg_list.pgname         left join docorate_list d              on cd.doctorate = docorate_list.doctorate         outer apply         (   values                 (ug_list.id),                 (pg_list.id),                 (ug_list.id)         ) d (degreeid);