locked
How can I write a sql query to select RRS feed

  • Question

  • User-19205220 posted

    tblAttendeeMaster
    AttendeeID   FirstName     Familyleadid
    530               Anu                0
    531               Manu             530
    532               Ramu             0
    533               Jeevan           532

    How can I write a sql query to select to display as follows (familyleadid values will be one from attendeeId)
    AttendeeID   FirstName      Familyleadid
    530               Anu
    531               Manu             Anu
    532               Ramu
    533               Jeevan            Ramu

    Friday, September 7, 2018 10:45 AM

Answers

  • User-2146987983 posted
    CREATE TABLE tblAttendeeMaster (
      AttendeeID	INT,
      FirstName		VARCHAR(10), 
      Familyleadid	INT
    );
    
    INSERT INTO tblAttendeeMaster(AttendeeID, FirstName, Familyleadid) VALUES(530, 'Anu', 0);
    INSERT INTO tblAttendeeMaster(AttendeeID, FirstName, Familyleadid) VALUES(531, 'Manu', 530);
    INSERT INTO tblAttendeeMaster(AttendeeID, FirstName, Familyleadid) VALUES(532, 'Ramu', 0);
    INSERT INTO tblAttendeeMaster(AttendeeID, FirstName, Familyleadid) VALUES(533, 'Jeevan', 532);
    
    ;with rcte as (
    Select AttendeeID, FirstName, CAST('' AS VARCHAR(10)) AS Familyleadid from tblAttendeeMaster where ISNULL(Familyleadid, 0) = 0
    Union all
    select e1.AttendeeID , e1.FirstName, CAST(e2.FirstName AS VARCHAR(10)) as Familyleadid from tblAttendeeMaster e1
     join rcte e2 on e2.AttendeeID = e1.Familyleadid  
      )
    
    Select * from rcte order by AttendeeID
    
    Drop table tblAttendeeMaster

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 7, 2018 12:11 PM

All replies

  • User-2146987983 posted
    CREATE TABLE tblAttendeeMaster (
      AttendeeID	INT,
      FirstName		VARCHAR(10), 
      Familyleadid	INT
    );
    
    INSERT INTO tblAttendeeMaster(AttendeeID, FirstName, Familyleadid) VALUES(530, 'Anu', 0);
    INSERT INTO tblAttendeeMaster(AttendeeID, FirstName, Familyleadid) VALUES(531, 'Manu', 530);
    INSERT INTO tblAttendeeMaster(AttendeeID, FirstName, Familyleadid) VALUES(532, 'Ramu', 0);
    INSERT INTO tblAttendeeMaster(AttendeeID, FirstName, Familyleadid) VALUES(533, 'Jeevan', 532);
    
    ;with rcte as (
    Select AttendeeID, FirstName, CAST('' AS VARCHAR(10)) AS Familyleadid from tblAttendeeMaster where ISNULL(Familyleadid, 0) = 0
    Union all
    select e1.AttendeeID , e1.FirstName, CAST(e2.FirstName AS VARCHAR(10)) as Familyleadid from tblAttendeeMaster e1
     join rcte e2 on e2.AttendeeID = e1.Familyleadid  
      )
    
    Select * from rcte order by AttendeeID
    
    Drop table tblAttendeeMaster

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 7, 2018 12:11 PM
  • User-62323503 posted

    Dear me_ritz,

    This can simply be achieved with left join

    select e1.AttendeeID , e1.FirstName, isnull(CAST(e2.FirstName AS VARCHAR(10)),'') as Familyleadid 
    from tblAttendeeMaster e1
    left join tblAttendeeMaster e2 on e2.AttendeeID = e1.Familyleadid
    Saturday, September 8, 2018 6:02 AM