none
Joing two table data RRS feed

  • Question


  •  have two table like below(Actualdetails,Budgetdetails)

    Table1:Actualdetails

    Code Code Name Year Month Amount
    1001   2018 JAN 456
    1002   2018 JAN 456
    1003   2018 JAN 235
    2002   2018 JAN 111
    2010   2018 JAN 34
    1001   2018 FEB 557
    1003   2018 FEB 64
    2002   2018 FEB 578
    2010   2018 FEB 234
    1001   2018 MAR 568
    1002   2018 MAR 124
    1003   2018 MAR 56
    2002   2018 MAR 1245
    2010   2018 MAR 678
    1001   2019 JAN 3467
    1003   2019 JAN 566
    2002   2019 JAN 1234
    2010   2019 JAN 456
    1003   2019 FEB 111
    2002   2019 FEB 34
    2010   2019 FEB 557
    1001   2019 MAR 64
    1002   2019 MAR 578
    1003   2019 MAR 234

    Table2: BudgetDetails

    Code  Code Name Year MONTH NAME BudgetAmount
    1001   2019 JAN 4577
    1001   2019 MAR 7865
    1002   2019 MAR 5657
    1003   2019 JAN 8963
    1003   2019 FEB 5434
    1003   2019 MAR 13445
    2002   2019 JAN 5422
    2002   2019 FEB 76432
    2010   2019 JAN 23456
    2010   2019 FEB 6543

    Out put should be like

    Code Code Name Year Month Amount Budget Amount
    1001   2018 JAN 456 0
    1002   2018 JAN 456 0
    1003   2018 JAN 235 0
    2002   2018 JAN 111 0
    2010   2018 JAN 34 0
    1001   2018 FEB 557 0
    1003   2018 FEB 64 0
    2002   2018 FEB 578 0
    2010   2018 FEB 234 0
    1001   2018 MAR 568 0
    1002   2018 MAR 124 0
    1003   2018 MAR 56 0
    2002   2018 MAR 1245 0
    2010   2018 MAR 678  
    1001   2019 JAN 3467 4577
    1003   2019 JAN 566 8963
    2002   2019 JAN 1234 5422
    2010   2019 JAN 456 23456
    1003   2019 FEB 111 5434
    2002   2019 FEB 34 76432
    2010   2019 FEB 557 6543
    1001   2019 MAR 64 7865
    1002   2019 MAR 578 5657
    1003   2019 MAR 234 13445

    please any help two to join this table

    Thanks

    Aruna

    Monday, October 21, 2019 10:45 AM

All replies

  • Get your T-SQL Fundamentals Third Edition..

    SELECT A.* ,
           B.* ,
           ISNULL(B.Col4, 0) AS Col4
    FROM   TableA A
           LEFT JOIN TableB B ON A.Col1 = B.Col1
                                 AND A.Col2 = B.Col2
                                 AND A.Col3 = B.Col3;

    Monday, October 21, 2019 10:56 AM
  • Hi

    Please find the below code which would fulfill your requirement.

    SELECT 
    	a.Code
    	,a.CodeName
    	,a.Year
    	,a.Month
    	,a.Amount
    	,ISNULL(b.BudgetAmount,0) AS BudgetAmount
    FROM Actualdetails a
    LEFT JOIN BudgetDetails b
    ON a.Code = b.Code AND a.Year = b.Year AND a.MONTH = b.MONTH

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, October 21, 2019 11:08 AM
  • Please post your question in scripts. Thanks.

    You can find how to post script in the following for your question:

    CREATE TABLE Actualdetails(
       Code      INTEGER  NOT NULL  
      ,Code_Name VARCHAR(1)
      ,[Year]      INTEGER  NOT NULL
      ,[Month]     VARCHAR(3) NOT NULL
      ,Amount    INTEGER  NOT NULL
    );
    INSERT INTO Actualdetails(Code,Code_Name,Year,Month,Amount) VALUES
     (1001,NULL,2018,'JAN',456)
    ,(1002,NULL,2018,'JAN',456)
    ,(1003,NULL,2018,'JAN',235)
    ,(2002,NULL,2018,'JAN',111)
    ,(2010,NULL,2018,'JAN',34)
    ,(1001,NULL,2018,'FEB',557)
    ,(1003,NULL,2018,'FEB',64)
    ,(2002,NULL,2018,'FEB',578)
    ,(2010,NULL,2018,'FEB',234)
    ,(1001,NULL,2018,'MAR',568)
    ,(1002,NULL,2018,'MAR',124)
    ,(1003,NULL,2018,'MAR',56)
    ,(2002,NULL,2018,'MAR',1245)
    ,(2010,NULL,2018,'MAR',678)
    ,(1001,NULL,2019,'JAN',3467)
    ,(1003,NULL,2019,'JAN',566)
    ,(2002,NULL,2019,'JAN',1234)
    ,(2010,NULL,2019,'JAN',456)
    ,(1003,NULL,2019,'FEB',111)
    ,(2002,NULL,2019,'FEB',34)
    ,(2010,NULL,2019,'FEB',557)
    ,(1001,NULL,2019,'MAR',64)
    ,(1002,NULL,2019,'MAR',578)
    ,(1003,NULL,2019,'MAR',234);
    
    
    CREATE TABLE BudgetDetails(
       Code         INTEGER  NOT NULL  
      ,Code_Name    VARCHAR(1)
      ,Year         INTEGER  NOT NULL
      ,MONTH_NAME   VARCHAR(3) NOT NULL
      ,BudgetAmount INTEGER  NOT NULL
    );
    INSERT INTO BudgetDetails(Code,Code_Name,[Year],[MONTH_NAME],BudgetAmount) VALUES
     (1001,NULL,2019,'JAN',4577)
    ,(1001,NULL,2019,'MAR',7865)
    ,(1002,NULL,2019,'MAR',5657)
    ,(1003,NULL,2019,'JAN',8963)
    ,(1003,NULL,2019,'FEB',5434)
    ,(1003,NULL,2019,'MAR',13445)
    ,(2002,NULL,2019,'JAN',5422)
    ,(2002,NULL,2019,'FEB',76432)
    ,(2010,NULL,2019,'JAN',23456)
    ,(2010,NULL,2019,'FEB',6543);
    
    --query
    select a.Code,a.Code_Name,a.[Year],a.[Month]
    ,a.Amount
    ,Coalesce(b.BudgetAmount,0) BudgetAmount
    from Actualdetails a 
    left join BudgetDetails b on a.Code=b.Code 
    and a.[Year]=b.[Year]
    and a.[Month]=b.[MONTH_NAME]
    
    
    drop table Actualdetails, BudgetDetails 
    

    Monday, October 21, 2019 2:39 PM
    Moderator
  • Hi , 
    Also you can try to use LEFT JOIN and CASE WHEN .

    CREATE TABLE Actualdetails(
       Code      INTEGER  NOT NULL  
      ,Code_Name VARCHAR(1)
      ,[Year]      INTEGER  NOT NULL
      ,[Month]     VARCHAR(3) NOT NULL
      ,Amount    INTEGER  NOT NULL
    );
    INSERT INTO Actualdetails(Code,Code_Name,Year,Month,Amount) VALUES
     (1001,NULL,2018,'JAN',456)
    ,(1002,NULL,2018,'JAN',456)
    ,(1003,NULL,2018,'JAN',235)
    ,(2002,NULL,2018,'JAN',111)
    ,(2010,NULL,2018,'JAN',34)
    ,(1001,NULL,2018,'FEB',557)
    ,(1003,NULL,2018,'FEB',64)
    ,(2002,NULL,2018,'FEB',578)
    ,(2010,NULL,2018,'FEB',234)
    ,(1001,NULL,2018,'MAR',568)
    ,(1002,NULL,2018,'MAR',124)
    ,(1003,NULL,2018,'MAR',56)
    ,(2002,NULL,2018,'MAR',1245)
    ,(2010,NULL,2018,'MAR',678)
    ,(1001,NULL,2019,'JAN',3467)
    ,(1003,NULL,2019,'JAN',566)
    ,(2002,NULL,2019,'JAN',1234)
    ,(2010,NULL,2019,'JAN',456)
    ,(1003,NULL,2019,'FEB',111)
    ,(2002,NULL,2019,'FEB',34)
    ,(2010,NULL,2019,'FEB',557)
    ,(1001,NULL,2019,'MAR',64)
    ,(1002,NULL,2019,'MAR',578)
    ,(1003,NULL,2019,'MAR',234);
    
    
    CREATE TABLE BudgetDetails(
       Code         INTEGER  NOT NULL  
      ,Code_Name    VARCHAR(1)
      ,Year         INTEGER  NOT NULL
      ,MONTH_NAME   VARCHAR(3) NOT NULL
      ,BudgetAmount INTEGER  NOT NULL
    );
    INSERT INTO BudgetDetails(Code,Code_Name,[Year],[MONTH_NAME],BudgetAmount) VALUES
     (1001,NULL,2019,'JAN',4577)
    ,(1001,NULL,2019,'MAR',7865)
    ,(1002,NULL,2019,'MAR',5657)
    ,(1003,NULL,2019,'JAN',8963)
    ,(1003,NULL,2019,'FEB',5434)
    ,(1003,NULL,2019,'MAR',13445)
    ,(2002,NULL,2019,'JAN',5422)
    ,(2002,NULL,2019,'FEB',76432)
    ,(2010,NULL,2019,'JAN',23456)
    ,(2010,NULL,2019,'FEB',6543);
    
    --query
    select a.Code,a.Code_Name,a.[Year],a.[Month]
    ,a.Amount
    ,case when b.BudgetAmount is null then 0 else b.BudgetAmount end  BudgetAmount
    from Actualdetails a 
    left join BudgetDetails b on a.Code=b.Code 
    and a.[Year]=b.[Year]
    and a.[Month]=b.[MONTH_NAME]
    
    
    drop table Actualdetails, BudgetDetails 
    /*
    Code        Code_Name Year        Month Amount      BudgetAmount
    ----------- --------- ----------- ----- ----------- ------------
    1001        NULL      2018        JAN   456         0
    1002        NULL      2018        JAN   456         0
    1003        NULL      2018        JAN   235         0
    2002        NULL      2018        JAN   111         0
    2010        NULL      2018        JAN   34          0
    1001        NULL      2018        FEB   557         0
    1003        NULL      2018        FEB   64          0
    2002        NULL      2018        FEB   578         0
    2010        NULL      2018        FEB   234         0
    1001        NULL      2018        MAR   568         0
    1002        NULL      2018        MAR   124         0
    1003        NULL      2018        MAR   56          0
    2002        NULL      2018        MAR   1245        0
    2010        NULL      2018        MAR   678         0
    1001        NULL      2019        JAN   3467        4577
    1003        NULL      2019        JAN   566         8963
    2002        NULL      2019        JAN   1234        5422
    2010        NULL      2019        JAN   456         23456
    1003        NULL      2019        FEB   111         5434
    2002        NULL      2019        FEB   34          76432
    2010        NULL      2019        FEB   557         6543
    1001        NULL      2019        MAR   64          7865
    1002        NULL      2019        MAR   578         5657
    1003        NULL      2019        MAR   234         13445
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, October 22, 2019 7:29 AM