January 5, 2008 at 10:46 pm
I have a single column with the following values;
003
003.1
003.2
003.3
003.31
003.32
003.33
003.34
etc...
the parent in this case is 003 and 003.3... the other values are children.
how do I put together a query to find the parents of the children
Regards,
Brent.
January 5, 2008 at 11:09 pm
If I understand what you're trying to accomplish, it's pretty straightforward. Everything to the left of the period is the parent, so look for that.
January 7, 2008 at 2:36 am
Why have only one column?
Why not one for parent and one for child?
Is your table structure immutable?
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 7, 2008 at 7:27 am
Probably very inefficient...
create table #c (val varchar(10))
insert #c (val) values('003')
insert #c (val) values('003.1')
insert #c (val) values('003.2')
insert #c (val) values('003.3')
insert #c (val) values('003.31')
insert #c (val) values('003.32')
insert #c (val) values('003.33')
insert #c (val) values('003.34')
select a.val from #c a
where a.val = (
select distinct substring(x.val,1,len(a.val))
from (
select val
from #c
where len(val) > len(a.val)
) x )
Derek
January 7, 2008 at 7:38 am
Brent McCracken
I do not see how your data structure is easily usable without jumping through hoops;
The only way to implement this with ease is with two columns - parent and child - in one table.
What is the result you need to achieve with this data?
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 7, 2008 at 8:36 am
it appears the key is implementating legal notation (1.2.1, 1.2.4, etc.). if so, two keys won't work.
mcfarland's original suggestion is best.
select ...
from Section as child join Section as parent
on parent.key = left( child.key, len(child.key) - charindex('.',reverse(child.key)))
we used this tactic a few years ago and found it best to always format each level number as 3 digits (1.2.1 becomes 001.002.001 making sorting of the hierarchy simple) and use an artificial primary key. This was done mainly because users could cut/move sections to different parents (e.g.: 1.2.1... is moved under 1.3.2 and becomes 1.3.2.1...).
January 7, 2008 at 8:47 am
antonio - though I do not disagree with you, I would still like to know the context of the question.
but i still think a parent child relationship would be better suited to two columns, and I'm not suggesting using it as a key.
even for your legal notation a relationship table would be possible
Parent Child
1.0 1.1
1.1 1.1.1
1.1 1.1.2
Every record has a parent entry and child entry in the relationship. There is no record for 1.0 on its own as it has no parent being a 'superparent', not being a child.
--Shaun
'lack of understanding is the root of evil'
Hiding under a desk from SSIS Implemenation Work :crazy:
January 7, 2008 at 9:55 am
if mcfarland is using that field as legal notation, then parent-child columns don't really help if the hierarchy exceeds two levels. using the legal notation column as an alternate key makes it very simple to find 2.1.* regardless of the number of levels below 2.1. if parent-child columns were used, recursion would be necessary.
January 7, 2008 at 10:02 am
I said I agree with you. 😀
Just doing my question everything/trust nothing routine. 😀
What is the purpose of the original request? How will the result be used?
p.s. original poster = Brent McCracken
Hiding under a desk from SSIS Implemenation Work :crazy:
January 7, 2008 at 10:10 am
antonio.collins (1/7/2008)
if mcfarland is using that field as legal notation, then parent-child columns don't really help if the hierarchy exceeds two levels. using the legal notation column as an alternate key makes it very simple to find 2.1.* regardless of the number of levels below 2.1. if parent-child columns were used, recursion would be necessary.
McFarland's doing no such thing, as McFarland was just responding to the original poster. 🙂
January 8, 2008 at 11:37 am
If your data is always numeric and always only has 1 decimal it may be simpler to convert the data to numeric data types and then compare.
Something like:
SELECT ColumnName
FROM Table
WHERE CAST(ColumnName AS DEC(15, 2))
= FLOOR(CAST(ColumnName AS DEC(15, 2)))
Todd Fifield
January 8, 2008 at 7:14 pm
The numbering system is flawed to begin with... 003.31 should not be the child of 003.3... they're both at the same "level" in legal numbering. If 003.31 is the "first" child of 003.3, it should be numbered 003.3.1 instead of 003.31....
... what happens if 003 has 31 children? 😛 BOOOOM!
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2008 at 1:51 am
My view on is that McFarland has answered the initial question but it is only treating a symptom and is not the cure, it solves the poster's issue.:D
The question is wrong on the basis of design and the modeling of the parent child relationship using one column is totally flawed.
A table having columns for Child, Parent and rest of data required, fulfils the design criteria in the easiest way.
if a parent record has 30 children there will be 30 records with that parent id in the Parent column.
if a record has no parent a record will exist with either null or some other value in the parent column.
This allows easy Select [Parent],[Child],.... where [Parent] <> "no parent" querying without any kind of string splicing overhead or other ambiguity.:D
Sorry to keep saying this - Design, design, design......
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 9, 2008 at 7:25 am
The question is wrong on the basis of design and the modeling of the parent child relationship using one column is totally flawed.
Actually, I believe the whole "adjacency model" thingy is wrong... I prefer Celko's "nested model"... has awsome performance.
Just a thought, though... Although all hierarchies are a bit of a pain, it's not uncommon to resolve smaller hierarchies (say, 800 levels) by concatenating all parents of a node to the left of the node identifier into a single column. It's certainly not the most effecient (that would be the nested model) but it can be made to work with some decent performance on smaller hierarchies. The legal numbering system is already perfectly preformed to do exactly that.
The legal numbering system has a side benefit... you don't have to worry about "hierarchical loops" where one child can have many parents. The legal numbering system is inherently 100% positional so there's no chance of such a loop, especially if you only have one column to store the position instead of using the adjacency model of having a column for the parent and a column for the child. Of course, a decent set of constraints will also prevent that in the adjacency model.
Like I said... just a thought.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2008 at 7:33 am
I've had to play with those kind of hierarchies as well, and I ended up going with...BOTH. A column that had the "composite key" with all of the parents in it, as well as a foreign surrogate key to the direct parent. It worked well since a lot of the operations involved dealing with the "close" relatives, and not every often having navigate the entire hierarchy. It also allowed some of my operations to actually use an index seek, since I didn't have functions in the ON clauses.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply