How to join 2 tables with different records in columns

Question

I have 2 tables
That name is:
AccountDetail and VoucheItem
table AccountDetail has records as follows

table VoucheItem has records as follows

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

Answer 1

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.

Answer 2

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  

Answer 3

  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)  

Answer 4

The codes above were for creating a table and adding records

Answer 5

Thanks Dear
I want two reporting methods
see this pics
1

2: