can somebody get the below output from the given table result.? RSS

• • Reply
  

  sivaganesh12...

  Member

  228 Points

  309 Posts

  can somebody get the below output from the given table result.?

  Jun 30, 2012 08:08 AM|LINK

  CREATE TABLE [dbo].[tblMembersRelation](
          [MembersRelationID] [int] IDENTITY(1,1) NOT NULL,
          [MemberID] [int] NULL,
          [ParentID] [int] NULL,
          [Level] [int] NULL)
  
  INSERT INTO [tblMembersRelation] ([MemberID],[ParentID])
  VALUES (1, null ), (2, 1), (3, 1), (4, 2), (5, 3), (6, 4)

   when we  give select * from [tblMembersRelation]

  MembersRelationID	MemberID	  ParentID	
  1	                         1	           NULL	
  2	                         2	             1	
  3	                         3	             1	
  4	                         4	             2	
  5	                         5	             3	
  6	                         6	             4	

  in a select  query   if i pass 6(memberId) i need to get the parentId for that memberId.

  so i used the below query.,

  select ParentID from tblMembersRelation  where  MemberID =6

  The output comes like,  

  ParentId

  =======

  4 

  Now., i need to pass the output as memberId as input and get the parentId for the input..

  For ex., If i pass 6 the parentId is 4 then i need pass the output automatically and it should get the parentId for 4 so the output is 2 then

  the 2 should pass as memberId then the output should comes as 1. same as i need to pass those value in a loop.

  So how can i do that?

  
  
  

• • Reply
  

  Anton Palyok

  Contributor

  2526 Points

  404 Posts

  Re: can somebody get the below output from the given table result.?

  Jun 30, 2012 08:59 AM|LINK

  You should take a ParentID. If it is not null then take one more parentID and check again.
  For storing results you can use temporary table.
  So final code can be like this:

  -- input parameter:
  DECLARE @memberId int = 6
  
  -- helper variables
  DECLARE @parentId int
  DECLARE @tmpTable TABLE (ParentId int)
  
  -- take current parentID
  SELECT @parentId = ParentId
  FROM @tblMembersRelation
  WHERE MemberID=@memberId
  
  -- save parentIDs until it is not null
  WHILE @parentId is not null
  BEGIN
     
     INSERT INTO @tmpTable VALUES(@parentId)
     
     select @parentId = ParentId FROM @tblMembersRelation where MemberID=@parentId
  	
  END
  
  -- show result
  SELECT * FROM @tmpTable

  As output should be a table like:

  ParentId
  --------
  4
  2
  1

• • Reply
  

  spapim

  Contributor

  2464 Points

  372 Posts

  Re: can somebody get the below output from the given table result.?

  Jun 30, 2012 06:50 PM|LINK

  Try:

  with CTE as
  (
      select t.MembersRelationID, t.MemberID, t.ParentID
      from tblMembersRelation as t
      where MemberID = 6
      
      union all
      
      select t.MembersRelationID, t.MemberID, t.ParentID
      from tblMembersRelation as t
      join CTE as c on c.ParentID = t.MemberID
  )
  
  select * from CTE
  order by ParentID

  Hope this helps. 

  www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial
  www.imobiliariasemsuzano.com.br
  • Marked as answer by sivaganesh1234 on Jul 03, 2012 12:46 PM