Auto JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Auto JOIN

Author

Topic

elhallouf
Starting Member

1 Post

Posted - 2008-05-20 : 08:41:24

I have a table (cust_name, cust_number, parent_number) like :
Name1--11111--22222
Name2--22222--55555
Name2--22222--55555
Name3--33333--33333
Name4--44444--11111
Name5--55555--44444
..
I want to add a column parent_name as :
Name1--11111--22222--Name2
Name2--22222--55555--Name5
Name2--22222--55555--Name5
Name3--33333--33333--Name3
Name4--44444--11111--Name1
Name5--55555--44444--Name4

As you can see, I try to get the corresponding name for the parent client accordingly to the relationshio cust_name, cust_number

Note that I can have the same cust_number repated in several rows, BUT the couple (cust_number, cust_name) is UNIQUE .. so the correspondance should be UNIQUE

This should be done by an AUTO JOIN on sql, but I am getting hard time to write the sql code since I couldn't get the same number or records..

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-20 : 08:49:40

[code]declare @t table
(cust_name varchar(20),
cust_number int, parent_number int,
Parent_Name varchar(20))

insert @t
select 'Name1',11111,22222, null union all
select 'Name2',22222,55555, null union all
select 'Name2',22222,55555, null union all
select 'Name3',33333,33333, null union all
select 'Name4',44444,11111, null union all
select 'Name5',55555,44444, null

update t1
set Parent_Name = t2.Cust_Name
from @t t1 join @t t2 on t2.cust_number = t1.parent_number

select * from @t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources