Need Query

  • 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

  • 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/

  • 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
  • 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]

  • 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 4 years, 12 months ago by  vs.satheesh.
  • 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]

  • Thank You

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

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