SEO

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.

NickName (dadada@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, esquel@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

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

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

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

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.

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.