Post reply

sql query

  • December 2, 2011 at 10:57 pm

    #252923

    declare @t1 TABLE(id int,region varchar(10),Lamt decimal(20,3))

    insert into @t1 values(1,'x',10.12)

    insert into @t1 values(1,'y',20.12)

    insert into @t1 values(1,'z',30.12)

    declare @t2 TABLE(id int,region varchar(10),lamt decimal(20,3))

    insert into @t1 values(1,'x',55.12)

    insert into @t1 values(1,'y',57.12)

    insert into @t1 values(1,'k',80.12)

    i want output like below format.

    ID Region Lamt Iamt

    1 x 10.12 55.12

    1 y 20.12 57.12

    1 z 30.12 0.00

    1 k 0.00 80.12

  • December 3, 2011 at 3:02 am

    #1416262

    Did you try the FULL OUTER JOIN syntax?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 3, 2011 at 5:09 am

    #1416277

    I tired but i was not got currect out .

  • December 3, 2011 at 5:12 am

    #1416278

    Please share what you've tried so far and where you get stuck.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 4, 2011 at 10:08 pm

    #1416470

    i was wrote below query

    select ISNULL(t.region,t1.region) Region,

    ISNULL(t.Lamt,0) Lamt,

    ISNULL(t1.lamt,0) lamt

    from @t1 t

    full outer join @t2 t1

    on t.id=t1.id

  • December 5, 2011 at 5:09 am

    #1416592

    Add one more column in your join and the SELECT statement like below

    SELECT ISNULL(t.id,t1.id) ID, ISNULL(t.region,t1.region) Region, ISNULL(t.Lamt,0) Lamt, ISNULL(t1.lamt,0) lamt

    FROM @t1 t

    FULL OUTER JOIN @t2 t1 ON t.id = t1.id AND t.region = t1.region

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 5, 2011 at 5:21 am

    #1416601

    Dear Subba Try This....

    declare @t1 TABLE(id int,region varchar(10),Lamt decimal(20,3))

    insert into @t1 values(1,'x',10.12)

    insert into @t1 values(1,'y',20.12)

    insert into @t1 values(1,'z',30.12)

    declare @t2 TABLE(id int,region varchar(10),lamt decimal(20,3))

    insert into @t2 values(1,'x',55.12)

    insert into @t2 values(1,'y',57.12)

    insert into @t2 values(1,'k',80.12)

    Select distinct ISNULL(t1.id,t2.id) AS ID,ISNULL(t1.Region,t2.Region) AS REGION ,t1.Lamt,t2.Lamt from @t2 t2 Full outer Join @t1 t1

    ON t1.id=t2.id AND t1.region=t2.region

    😛

  • December 5, 2011 at 5:26 am

    #1416605

    Hi,

    Try this,

    SELECT ISNULL(T1.id,T2.id) id,ISNULL(T1.region,T2.region) region,ISNULL(T1.Lamt,0.00) Lamt,ISNULL(T2.lamt,0.00) Iamt

    FROM @t1 T1

    FULL OUTER JOIN @t2 T2 ON T1.id = T2.id AND T1.region = T2.region

    Regards,

    Adil

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

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