My boss beats me

[NickName]

I thought I'm pretty good with sql. Was presented with a little
challenge the other day, a colleague asked me to create a parent to
child relationship for a table without changing db schema. The table
looks like this
pkID parentID altPKfield
1 null abc
2 1 def

It's obvious parent to child relationship via pkID and parentID columns
would do a perfect job, however, he specifically asked not to use pkID
but altPKfield instead.

My first thought was to create a sort of 'ghost column' for 'alt parent
field', then, it goes like using a UDF to get pkID value, use temp
table or table variable, it worked but took quite a bit of time.

The next day, the colleague told me our boss has another solution, that
is, he joins this same table twice to geneate a fourth column of
another pkID, and it works perfect and it's very elegant compared to my
approach.

Question here is, would you know/use his approach without learning this
techque? Second question is, would there be any downside on using
this alternative approach of creating a parent to child relationship
for the same table not using PK column?
Thanks.

[Erland Sommarskog]

NickName (da****@rock.com) writes:

The next day, the colleague told me our boss has another solution, that
is, he joins this same table twice to geneate a fourth column of
another pkID, and it works perfect and it's very elegant compared to my
approach.

Question here is, would you know/use his approach without learning this
techque? Second question is, would there be any downside on using
this alternative approach of creating a parent to child relationship
for the same table not using PK column?

So what did his solution look like?

I don't see the point of not using pkID/parentID, but then again, I
don't know the actual business problem.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[NickName]

Hey Erland, haven't use the NG for a while hope you're doing well.
I think his query looks like this,

select t1.pkID, t2.pkID as pkID2
from theTbl t1 INNER JOIN theTbl t2
on t1.pkID = t2.pkID

[Robert Klemme]

NickName wrote:

Hey Erland, haven't use the NG for a while hope you're doing well.
I think his query looks like this,

select t1.pkID, t2.pkID as pkID2
from theTbl t1 INNER JOIN theTbl t2
on t1.pkID = t2.pkID

Are you sure about the join on the same column? That would only join
every row with itself not very useful and certainly not helpful in
connecting records in a hierarchy.

Kind regards

robert

[NickName]

Robert,

Probably you're right, I don't have his query handy, I'll check his
query when I return to HQ and see him.
Thanks.

Don

[--CELKO--]

Havbe you looked at the nested sets model for hierarchies? Might want
to get a copy of TREES & HIERARCHIES IN SQL for other, easier ways to
do this.

[pb648174]

That book and "other, easier" is an interesting statement. Nested sets
is not easy to use, so make sure you check out the chapter on the
probability of a collusion when inserting a new node before you decide
it's easier.