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. 6 1133
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: xeys_00 |
last post by:
I'm a manager where I work(one of the cogs in a food service company).
The boss needed one of us to become the "tech guy", and part of that is
writing small windows programs for the office. He...
|
by: Domenic G. |
last post by:
I was wondering how many Oracle DBAs out there have bosses that are
complete doe-doe birds? My current boss is a beaut -- has absolutely
no concept of Oracle, keeps flipping back and forth from...
|
by: john bailo |
last post by:
I just installed mono from ximian on my redHat 9 workstation
and wrote a simple program from the interesting book
ado.net in c# by Mahesh Chand.
mono is fun
( is there ado for linux ? ...
|
by: mathilda |
last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than
SELECT all other factors being equal. I disagree. We are linking an
Access front end to a SQL Server back end and normally are...
|
by: Daniel Kaseman |
last post by:
I've tried to get my boss to like me, but alas, my efforts proved futile.
Help!
-Thanks
|
by: John |
last post by:
Is there a good implementation of the boss-worker model
in C++ using pthreads? I have an array of objects A that each
need processing and I have p threads running simultaneosly.
Any object can be...
|
by: Dave |
last post by:
RAID 5 beats RAID 10
Can I get some feedback on these results? We were having some serious
IO issues according to PerfMon so I really pushed for RAID 10. The
results are not what I expected.
...
|
by: gandalf |
last post by:
With reason of the enormous effort carried out to visit the Argentinean
city of Córdoba, to participate in the Meeting of MERCOSUR, in the
closing of the Summit of the Towns in the historical...
|
by: jehugaleahsa |
last post by:
Hello:
I had an hour-long discussion with my boss today. Last night, right
before I dozed off, I realized some of his code resulted in duplicate
processing. I tried to explain it to him and he...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |