Need Query

Question

Need Query

vs.satheesh

SSCrazy

Points: 2392
More actions
April 4, 2019 at 2:38 pm

#2779750

Dear All
In my scenario i have two tables
create table #tblCombineCourseMaster(Combinecourseid char(9),Courseid char(9))
insert into #tblCombineCourseMaster values ('110100013','110100009')
insert into #tblCombineCourseMaster values ('110100013','110100012')
create table #tblSectionCoursemaster(courseid char(9),subcourseid char(9) )
insert into #tblCombineCourseMaster values ('110100009','110100010')
insert into #tblCombineCourseMaster values ('110100009','110100011')
I want following output. I just want one main Combinecourseid second column for all the child records..
create table #outputtable (Combinecourseid char(9),Subcourseid char(9) )
insert into #outputtable values ('110100013','110100012')
insert into #outputtable values ('110100013','110100010')
insert into #outputtable values ('110100013','110100011')
select * from #outputtable

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

Is this homework, and what have you tried so far?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

This is easily achieved with a LEFT JOIN and ISNULL combination. Of course, assuming that the second batch of inserts is meant for #tblSectionCoursemaster instead of #tblCombineCourseMaster .

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

It is not clear why you don't want 110100009 to appear in the output. I will make the assumption that this an error in your expected output.

If I understand the problem correctly, you want to see all children in a recursive structure listed together with the top node. Here is a query for this:

WITH recurse AS (

SELECT a.Combinecourseid AS topnode, a.Courseid

FROM #tblCombineCourseMaster a

WHERE NOT EXISTS (SELECT *

FROM #tblCombineCourseMaster b

WHERE b.Courseid = a.Combinecourseid)

UNION ALL

SELECT r.topnode, CM.Courseid

FROM recurse r

JOIN #tblCombineCourseMaster CM ON r.Courseid = CM.Combinecourseid

)

SELECT topnode, Courseid

FROM recurse

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

vs.satheesh SSCrazy Points: 2392 More actions · Answer 4

Sorry i did mistake my Question now corrected ,In my scenario i have two tables

create table #tblCombineCourseMaster(Combinecourseid char(9),Courseid char(9))

insert into #tblCombineCourseMaster values (‘110100013',’110100009’)

insert into #tblCombineCourseMaster values (‘110100013',’110100012’)

create table #tblSectionCoursemaster(courseid char(9),subcourseid char(9) )

insert into #tblSectionCoursemaster values (‘110100009',’110100010’)

insert into tblSectionCoursemaster values (‘110100009',’110100011’)

I want following output. I just want one main Combinecourseid second column for all the child records..

create table #outputtable (Combinecourseid char(9),Subcourseid char(9) )

insert into #outputtable values (‘110100013',’110100012’)

insert into #outputtable values (‘110100013',’110100010’)

insert into #outputtable values (‘110100013',’110100011’)

select * from #outputtable

This reply was modified 5 years, 7 months ago by vs.satheesh.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

Somehow I failed to observe that there were two tables. Here is a query to meet your needs:

SELECT CM.Combinecourseid, coalesce(SC.subcourseid, CM.Courseid) AS Subcourseid

FROM #tblCombineCourseMaster CM

LEFT JOIN #tblSectionCoursemaster SC ON CM.Courseid = SC.courseid

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

vs.satheesh SSCrazy Points: 2392 More actions · Answer 6

vs.satheesh

SSCrazy

Points: 2392

April 5, 2019 at 12:46 pm

#2861360

Thank You