How to join 2 tables with different records in columns

Shahab a 241 Reputation points
2022-08-18T08:05:05.59+00:00

I have 2 tables
That name is:
AccountDetail and VoucheItem
table AccountDetail has records as follows
232313-1.png

table VoucheItem has records as follows

232260-2.png

Now I want to get report from code 4000 which is stored in two different columns using join.
how to do it

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 21,851 Reputation points Microsoft Vendor
    2022-08-18T08:48:21.147+00:00

    Hi @Shahab a
    For report1, try this:

    ;WITH CTE AS  
    (  
     SELECT ID,Rows,[DESC],Debit,Credit,C.*  
     FROM VoucheItem CROSS APPLY(VALUES(AdRef1,'AdRef1'),  
                                        (AdRef2,'AdRef2'),  
    									(AdRef3,'AdRef3'),  
    									(AdRef4,'AdRef4'))C(AdRef_Value,AdRef_Type)  
    )  
    SELECT ADId,ADDetail,[DESC],Debit,Credit,SUM(Debit-Credit)OVER(PARTITION BY ADId,ADDetail ORDER BY [DESC] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS Result  
    FROM AccountDetail A JOIN CTE C ON A.ADId=C.AdRef_Value  
    WHERE ADId=4000      
    

    For report2, check this:

    ;WITH CTE AS  
    (  
     SELECT ID,Rows,[DESC],Debit,Credit,C.*  
     FROM VoucheItem CROSS APPLY(VALUES(AdRef1,'AdRef1'),  
                                        (AdRef2,'AdRef2'),  
    									(AdRef3,'AdRef3'),  
    									(AdRef4,'AdRef4'))C(AdRef_Value,AdRef_Type)  
    )  
    SELECT ADId,ADDetail,SUM(Debit)Debit,SUM(Credit)Credit,SUM(Debit)-SUM(Credit)AS Result  
    FROM AccountDetail A JOIN CTE C ON A.ADId=C.AdRef_Value  
    WHERE ADId=4000  
    GROUP BY ADId,ADDetail  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


6 additional answers

Sort by: Most helpful
  1. Shahab a 241 Reputation points
    2022-08-18T08:16:50.68+00:00
    USE [DataBase1]  
    GO  
      
    SET ANSI_NULLS ON  
    GO  
      
    SET QUOTED_IDENTIFIER ON  
    GO  
      
    CREATE TABLE [dbo].[AccountDetail](  
    	[AdId] [int] NOT NULL,  
    	[Type] [smallint] NOT NULL,  
    	[AdDetail] [nvarchar](50) NOT NULL,  
     CONSTRAINT [PK_AccountDetail] PRIMARY KEY CLUSTERED   
    (  
    	[AdId] ASC  
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
    ) ON [PRIMARY]  
      
    GO  
    -------------------------------  
    USE [DataBase1]  
    GO  
      
    SET ANSI_NULLS ON  
    GO  
      
    SET QUOTED_IDENTIFIER ON  
    GO  
      
    CREATE TABLE [dbo].[VoucheItem](  
    	[Id] [bigint] IDENTITY(1,1) NOT NULL,  
    	[Rows] [int] NOT NULL,  
    	[AdRef1] [int] NOT NULL,  
    	[AdRef2] [int] NOT NULL,  
    	[Adref3] [int] NOT NULL,  
    	[Adref4] [int] NULL,  
    	[Desc] [nvarchar](50) NOT NULL,  
    	[Debit] [bigint] NOT NULL,  
    	[Credit] [bigint] NOT NULL,  
     CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED   
    (  
    	[Id] ASC  
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
    ) ON [PRIMARY]  
      
    GO  
      
    ALTER TABLE [dbo].[VoucheItem]  WITH CHECK ADD  CONSTRAINT [FK_VoucheItem_AccountDetail] FOREIGN KEY([AdRef1])  
    REFERENCES [dbo].[AccountDetail] ([AdId])  
    GO  
      
    ALTER TABLE [dbo].[VoucheItem] CHECK CONSTRAINT [FK_VoucheItem_AccountDetail]  
    GO  
      
    ALTER TABLE [dbo].[VoucheItem]  WITH CHECK ADD  CONSTRAINT [FK_VoucheItem_AccountDetail1] FOREIGN KEY([AdRef2])  
    REFERENCES [dbo].[AccountDetail] ([AdId])  
    GO  
      
    ALTER TABLE [dbo].[VoucheItem] CHECK CONSTRAINT [FK_VoucheItem_AccountDetail1]  
    GO  
      
    ALTER TABLE [dbo].[VoucheItem]  WITH CHECK ADD  CONSTRAINT [FK_VoucheItem_AccountDetail2] FOREIGN KEY([Adref3])  
    REFERENCES [dbo].[AccountDetail] ([AdId])  
    GO  
      
    ALTER TABLE [dbo].[VoucheItem] CHECK CONSTRAINT [FK_VoucheItem_AccountDetail2]  
    GO  
      
    ALTER TABLE [dbo].[VoucheItem]  WITH CHECK ADD  CONSTRAINT [FK_VoucheItem_AccountDetail3] FOREIGN KEY([Adref4])  
    REFERENCES [dbo].[AccountDetail] ([AdId])  
    GO  
      
    ALTER TABLE [dbo].[VoucheItem] CHECK CONSTRAINT [FK_VoucheItem_AccountDetail3]  
    GO  
    
    0 comments No comments

  2. Shahab a 241 Reputation points
    2022-08-18T08:19:49.34+00:00
      INSERT INTO [dbo].[VoucheItem] VALUES  (1,1000,2000,3000,4000,'This is Test',500000,0);  
        INSERT INTO [dbo].[VoucheItem] VALUES  (2,2000,3000,4000,NULL,'THis is Test2',0,70000)  
    
    0 comments No comments

  3. Shahab a 241 Reputation points
    2022-08-18T08:20:46.847+00:00

    The codes above were for creating a table and adding records


  4. Shahab a 241 Reputation points
    2022-08-18T08:33:36.08+00:00

    Thanks Dear
    I want two reporting methods
    see this pics
    1
    232199-1.png

    2:
    232294-2.png

    0 comments No comments