Parent to Child mappings identification in a single query

Hi,

I want to retrive the output in a single SQL query.

I had two columns in a table; I used some values of column1 in column2.
I want to identify all the column1 vlaues in Left Hand side and column2 values in Right Hand side, with a single query.

example:

MasterTable

ParentRow ChildRow
--
A B
C D
E F
G C
H E
M G

Output required:

ParentRow ChildRow
--
A B
C D
E F
G D (C mapped tfrom D)
H F
M D (G mapped from C, and C mapped from D)

To get the above output, I written query using WHILE...[BREAK|CONTINUE] concept until I get @.@.rowcount become zero.

I am looking for better performace and to get the output in a single query, instead of multiple passes.

Can any one suggest good tools and articles on performance tuning techniques?

Thanks
Sreekanth

Waht is your database? SQL Server 2000 or 2005?

|||

If you use SQL Server 2005,

Code Snippet

Create Table #data (

[ParentRow] char ,

[ChildRow] char

);

Insert Into #data Values('A','B');

Insert Into #data Values('C','D');

Insert Into #data Values('E','F');

Insert Into #data Values('G','C');

Insert Into #data Values('H','E');

Insert Into #data Values('M','G');

;WITH TREE

as

(

Select

[ParentRow],

[ChildRow],

[ChildRow] as [Alternate],

Cast('*' + '\' + '*' as varchar(max)) Path

from #Data

Union ALL

Select

data.[ParentRow],

data.[ChildRow],

Tree.[Alternate],

Cast(Path + '\' + '*' as varchar(max)) Path

from #Data as data

Join Tree On Data.[ChildRow] = Tree.[ParentRow]

)

,Tree2 as

(

Select

[ParentRow],

[ChildRow],

[Alternate],

Len(path) as PathLen,

Max(Len(path)) Over(Partition BY [ParentRow], [ChildRow]) MaxLen

from Tree

)

select

[ParentRow],

[Alternate]

from

Tree2

Where

PathLen=MaxLen

Drop table #data

|||

If you use SQL server 2000,

Code Snippet

create table datatable (

[parentrow] char ,

[childrow] char

);

insert into datatable values('A','B');

insert into datatable values('C','D');

insert into datatable values('E','F');

insert into datatable values('G','C');

insert into datatable values('H','E');

insert into datatable values('M','G');

go

create function findalternate(@.childvalue char)

returns char

as

begin

declare @.return as char;

select @.return = childrow from datatable where parentrow=@.childvalue;

if @.return is null

return @.childvalue

else

return dbo.findalternate(@.return)

return null;

end

go

select *, dbo.findalternate(childrow) from datatable

|||

I am using SQL 2005 Server.

Thank you for providing the T-SQL.