CODEWITHSUNDEEP
sql-server
Raj
Raj

Reputation: 343

How to fetch self referenced column value in sql

I have two tables with column

CREATE TABLE Dept(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Next_ID] [int] NULL,
    [Name] [varchar](50) NOT NULL,
    [Bundle_ID] [int] NULL
)

And 

CREATE TABLE Bundle(
    [Bundle_ID] [int] NOT NULL,
    [Bundle_Name] [varchar](40) NOT NULL
)

I would like to fetch nextID name so i tried 

SELECT dept.ID, dept.Next_ID, currentbundle.Bundle_Name CurrentBundleName
FROM Dept dept
      join Bundle currentbundle on currentbundle.Bundle_ID = dept.Bundle_ID
      join dept dept1 on dept1.Next_ID=dept.ID

With this, I get only currentBundleName. How to fetch nextbundlename?

I would like to have output like this

ID       NextID      CurrentBundleName    NextBundleName
********************************************************
1        3           template             excel
3        4           excel                word
4        NULL        word                 NULL

Upvotes: 1

Views: 43

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

Please try this :

SELECT d.id, 
d.next_id, 
b.Bundle_Name current_bundle_name, 
b1.Bundle_Name AS next_bundle_name
FROM dept d
INNER JOIN bundle b ON b.Bundle_ID = d.Bundle_ID
LEFT JOIN bundle b1 ON b1.Bundle_ID = d.Next_ID

Upvotes: 1

Max Sorin
Max Sorin

Reputation: 1052

CREATE TABLE #Dept(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Next_ID] [int] NULL,
    [Name] [varchar](50) NOT NULL,
    [Bundle_ID] [int] NULL
)


CREATE TABLE #Bundle(
    [Bundle_ID] [int] NOT NULL,
    [Bundle_Name] [varchar](40) NOT NULL
)

INSERT INTO #Bundle
        ( Bundle_ID, Bundle_Name )
VALUES  
( 1, 'One'  ),
( 2, 'Two'  ),
( 3, 'Three'  ),
( 4, 'Four'  )

INSERT INTO #Dept
        ( Next_ID, Name, Bundle_ID )
VALUES  
( NULL,  'First',  1),
( 1,  'Second',  2),
( 2,  'Third',  3),
( 3,  'Fouth',  4)


select 
    d.ID,
    d.Name          AS DeptName,
    d2.ID           AS NextDeptId,
    d2.Name         AS NextDeptName,
    b.Bundle_Name   AS BundleName,
    b2.Bundle_Name  AS NextBundleName
FROM #Dept d
JOIN #Bundle b  ON b.Bundle_ID = d.Bundle_ID
LEFT JOIN #Dept d2  ON d2.id=d.Next_ID
LEFT JOIN #Bundle b2 ON b2.Bundle_ID = d2.Bundle_ID

DROP TABLE #Bundle
DROP TABLE #Dept

Corrected Results:

ID  DeptName    NextDeptId  NextDeptName    BundleName  NextBundleName
1   First       NULL        NULL            One         NULL
2   Second      1           First           Two         One
3   Third       2           Second          Three       Two
4   Fouth       3           Third           Four        Three

Upvotes: 1

Related Questions