Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This challenge refers to a real-world problem I came across recently. The requirement was to identify all 'direct' or 'indirect' parents of a given child node and all its siblings.

TSQL Beginners Challenge 9 - Find the Parents and Adjacent Members of the Node

TSQL Beginners Challenge 9 - Find the Parents and Adjacent Members of the Node

Jun 6 2010 2:00AM by Niladri Biswas   

Introduction

This challenge refers to a real-world problem I came across recently. The requirement was to identify all 'direct' or 'indirect' parents of a given child node and all its siblings.

To give a better idea of the problem consider the following.

  
				A(1)
				|
---------------------------------
|				|				|
B(2)			R(3)			C(4)
|								|
--------------------			Child1OfC(9)
|		           |
Child1OfB(5)	Child2OfB(6)
|
----------
|	     |
   D(7)	E(8)	

Given ChildId = 5 the Output will be A,B,R,C, Child1OfB, Child2OfB.

Because A,B,R,C are all Parents of 5 i.e. Child1OfB in some way (direct or indirect) and Child2OfB is the adjacent of Child1OfB.

However, the children of Child1OfB (if any is present) will not be consider as they are it’s children.

Sample Data

ParentId ChildId Name
-------- ------- ------------------	
NULL	 1	     Niladri Biswas
1		 2	     Piyush Ghosh
1		 3	     Agnish Basu
1		 4	     Deepak Kumar Goyal
2		 5	     Sachin Srivastav
2		 6	     Nishant Mandilwar
5		 7	     Arindam Pal
5		 8	     Mahi Sharma
3		 9	     Mahima Roy
3		 10	     Simran Motilal
9		 11	     Raj Malhotra
9		 12	     Sharmistha Roy
10		 13	     Preeti Sen
10		 14	     Holly Huggins

Given a @ChildId = 6 as Parameter,

Expected Results

Id	Name
---	-------------------------
1	Niladri Biswas
3	    Agnish Basu
4	    Deepak Kumar Goyal
2	    Piyush Ghosh
6	        Nishant Mandilwar
5	        Sachin Srivastav

Rules

  1. The solution should work on SQL Server 2005 and above.
  2. Column names should respect the desired output shown.
  3. Output must be sorted in DESCENDING ORDER of Name.

Sample Script

Use the following script to generate the sample data.

DECLARE @ParentChild TABLE(Parentid INT,Childid INT,Name Varchar(20) );
INSERT INTO @ParentChild 
SELECT NULL,1,'Niladri Biswas' UNION ALL 
SELECT 1,2,'Piyush Ghosh' UNION ALL  
SELECT 1,3,'Agnish Basu' UNION ALL  
SELECT 1,4,'Deepak Kumar Goyal'  UNION ALL
SELECT 2,5,'Sachin Srivastav' UNION ALL 
SELECT 2,6,'Nishant Mandilwar' UNION ALL 
SELECT 5,7,'Arindam Pal' UNION ALL 
SELECT 5,8,'Mahi Sharma' UNION ALL 
SELECT 3,9,'Mahima Roy' UNION ALL 
SELECT 3,10,'Simran Motilal' UNION ALL 
SELECT 9,11,'Raj Malhotra' UNION ALL
SELECT 9,12,'Sharmistha Roy' UNION ALL 
SELECT 10,13,'Preeti Sen'  UNION ALL 
SELECT 10,14,'Holly Huggins'

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”.

Notes

Tags:Puzzles, TSQL Beginners Challenge, TC, TSQL Beginners Challenge 9

Niladri Biswas
7 · 21% · 6720
TSQL Beginners Challenge 9 - Find the Parents and Adjacent Members of the Node , 5.0 out of 5 based on 0 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]