Post reply

Twice join same table with left join issue

  • December 3, 2013 at 1:51 am

    #280752

    Hi All ,

    I have table with data like below

    table name : #tmp_h

    Namemarksmonthsubject

    Kumar801maths

    Guna701science

    Kumar502physics

    Guna402science

    Query

    select t1.name,t1.marks-t2.marks as Marks,CONVERT(VARCHAR(19),

    t1.month)+'~'+CONVERT(VARCHAR(19),

    t2.month) as Month,

    t1.subject from #tmp_h t1

    left join #tmp_h t2 on t1.name=t2.name and t1.subject=t2.subject

    where t1.month<>t2.month

    Actual output

    nameMarksMonthsubject

    Guna301~2science

    Guna-302~1science

    I need output like below.

    nameMarksMonthsubject

    Kumar801~2maths

    Kumar402~1science

    Guna301~2science

    Guna-302~1science

    Kumar801~2maths

    Kumar402~1science

    these 2 rows not added , I need these details also .. Kindly advise what need to change in the query

  • December 3, 2013 at 1:58 am

    #1670285

    Please don't cross post. It just wastes people's time and fragments replies

    No replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1519100-391-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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