CODEWITHSUNDEEP
sqlsql-server-2008-r2
Leventis1984
Leventis1984

Reputation: 82

How to join data from the same table and same column to two different columns

LN_REF_NO is the reference number of the loan. LN_REF_NO must have one ACNO AND ONE LRAC.

BOTH OF THEM ARE STORED TO ACCOUNTS UNDER ACNO COLUMN.

HOW I WILL SEPARATE THEM ? PLS LOOK THE IMAGE 

ALTER PROCEDURE sp_FinalLoanPayment
AS

DECLARE @LN_REF_NO char(13)

DECLARE @TempA
    TABLE (
        [LN_REF_NO] [char](13),
        [Status] [varchar](15),
        [ACNO] [varchar](21),
        [ACNOBalance] [dbo].[Amount]
  )

DECLARE @TempL
    TABLE (
        [LN_REF_NO] [char](13),
        [Status] [varchar](15),
        [LRAC] [varchar](21),
        [LRACBalance] [dbo].[Amount]
  )
DECLARE @TempAll
    TABLE (
        [LN_REF_NO] [char](13),
        [Status] [varchar](15),
        [ACNO] [varchar](21),
        [ACNOBalance] [dbo].[Amount],
        [LRAC] [varchar](21),
        [LRACBalance] [dbo].[Amount]

  )

INSERT INTO @TempL (LN_REF_NO ,Status,LRAC,LRACBalance)
SELECT Loan.LN_REF_NO, Loan.Status,ACCOUNTS.ACNO ,ACCOUNTS.BALANCE 
FROM Loan
INNER JOIN Accounts
ON LOAN.LRAC = ACCOUNTS.ACNO  
WHERE Loan.Status ='Paid' AND ACCOUNTS.BALANCE>0 


INSERT INTO @TempA (LN_REF_NO ,Status,ACNO,ACNOBalance)
SELECT Loan.LN_REF_NO, Loan.Status,ACCOUNTS.ACNO ,ACCOUNTS.BALANCE 
FROM Loan
INNER JOIN Accounts
ON LOAN.ACNO = ACCOUNTS.ACNO  
WHERE Loan.Status ='Paid' AND ACCOUNTS.BALANCE<0  

-- START CURSOR 1
DECLARE @cursor CURSOR 
SET @cursor = CURSOR FOR
select LN_REF_NO from @TempL
OPEN @cursor

FETCH NEXT
FROM @cursor INTO 
        @LN_REF_NO

WHILE (@@FETCH_STATUS = 0)
BEGIN

INSERT INTO @TempAll (LN_REF_NO ,Status,ACNO,ACNOBalance)
select LN_REF_NO,Status,ACNO,ACNOBalance
from @TempA
where LN_REF_NO = @LN_REF_NO 

FETCH NEXT
FROM @cursor INTO 
        @LN_REF_NO

END


CLOSE @cursor
DEALLOCATE @cursor

-- CURSOR 1 END


select * from @TempAll order by LN_REF_NO

Results:

enter image description here

Upvotes: 1

Views: 1208

Answers (1)

marc_s
marc_s

Reputation: 754488

I think you should be able to get this data as you need it with this query:

SELECT
    ln.LN_REF_NO, ln.Status, a1.ACNO, a1.BALANCE, a2.ACNO, a2.BALANCE
FROM
    dbo.Loan ln
INNER JOIN
    dbo.Accounts a1 ON ln.LRAC = a1.ACNO
INNER JOIN
    dbo.Accounts a2 ON ln.ACNO = a2.ACNO
WHERE
    ln.Status = 'Paid'
    AND a1.Balance > 0
    AND a2.Balance > 0

You basically just join to the Accounts table twice - once for the link to Loan.LRAC and a second time for the link on Loan.ACNO.

Based on those two joins, you just pick those columns from all joined tables that you need - and that's really all there is! No cursor, no temp tables - nothing like that needed !

Upvotes: 1

Related Questions