parent and child rows in same table

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 2005 Forums

Transact-SQL (2005)

parent and child rows in same table

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-03-25 : 12:18:38

Hi there

I am working with a certain I have inherited with following structure


Project#            Ref#
1                   222
                    222
                    222
                    222

First row with project# is the 'parent' row all children rows are figured out by looking at any rows with project# blank but Ref# equal to parent row, since parent row has Project# no blank. Sort of self referencing table.

What I am trying to do is do a self join so that I can get parent row tables, but I want to on the fly give the children rows parent rows project#. How do I got about doing that?

Thank you

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:23:21

[code]select t.[Ref#],t1.[Project#]
from yourtable t
join yourtable t1
on t1.[ref#] = t.[ref#]
and t1.[Project#] is not null[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/