By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

My boss beats me

P: n/a

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.

May 22 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
May 22 '06 #2

P: n/a
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

May 23 '06 #3

P: n/a
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

May 23 '06 #4

P: n/a
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

May 24 '06 #5

P: n/a
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.

May 24 '06 #6

P: n/a
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.

May 25 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.