Need help with sorting/querying robvolk's lineage

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with sorting/querying robvolk's lineage

Author

Topic

jyaki
Starting Member

5 Posts

Posted - 2004-12-23 : 14:48:47

I am hoping that there are some knowledgeable helpers on Rob's "More Trees & Hierarchies in SQL" [url]http://www.sqlteam.com/item.asp?ItemID=8866[/url] script here.
Basically, I've implemented his method and everything works great. However, the problem I'm having is in the querying and displaying the results in a particular fashion. I am trying to create a "sitemap" with all subdepartments under the correct parent departments.

Using the following code:


"SELECT D.dept_id, D.name, T.lineage, T.depth FROM k_depts D " & _
"INNER JOIN k_dept_tree T ON D.dept_id = T.dept_id " &_
"WHERE EXISTS (SELECT T.dept_id FROM k_dept_tree T WHERE T.dept_id = D.dept_id AND T.Lineage Like '%" & parentDept_id & "%') " & _
"ORDER BY T.lineage + Ltrim(Str(T.Node,6,0))"

I get these results:

As you can see, all of the sub departments that are directly under the parent departments are listed first. Then, all sub-subdepartments (depth=2) are listed afterwards. How can I get them to order correctly and in alphabetical order at each level...something like this:

I indented it so it's easier to understand.

There has to be a way to accomplish this in the query, right? Thanks for all your help!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-23 : 14:54:13

i think all you ned to do is order by lineage...
but your problem is that the lineage should include the current DeptId...

so your sample data should be:
SubDeptId: 5028 Lineage: /1150/5028
SubDeptId: 5029 Lineage: /1150/5029
SubDeptId: 5030 Lineage: /1150/5030
SubDeptId: 5031 Lineage: /1150/5031
...
SubDeptId: 6383 Lineage: /1150/5028/6383
...
etc

Corey

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-23 : 14:59:41

Don't know about Rob's structure but maybe you can use this
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

I suspect it's because you need the entity id in the sort order
you need to order by Lineage + SubDeptID and add space(depth * 5) at the beginning of the line.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-23 : 15:59:59

and this:

http://www.sevethnight.com/treestructs.asp

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-23 : 16:23:34

quote:

Need help with sorting/querying robvolk's lineage

Yes, most of us here often wonder where Rob came from ..... a true mystery that may never be solved!

- Jeff

jyaki
Starting Member

5 Posts

Posted - 2004-12-23 : 16:25:17

Thanks for the quick reply guys.
If possible, I'd like to keep the same format since I am using it elsewhere as well.
nr, I tried what you suggested but I'm not quite understanding it. How exactly should I change the query?


"SELECT D.dept_id, D.name, T.lineage, T.depth FROM k_depts D " & _
"INNER JOIN k_dept_tree T ON D.dept_id = T.dept_id " &_
"WHERE EXISTS (SELECT T.dept_id FROM k_dept_tree T WHERE T.dept_id = D.dept_id AND T.Lineage Like '%" & parentDept_id & "%') " & _
"ORDER BY T.lineage + Ltrim(Str(T.Node,6,0))"

What exactly does the Space(T.Depth*5) do?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-24 : 05:34:13

"What exactly does the Space(T.Depth*5) do?"

it indents the 'children' rows....for display purposes.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-24 : 09:20:55

Not sure what Node is but maybe
ORDER BY T.lineage + right(' ' + (convert(varchar(4),D.dept_id),4)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jyaki
Starting Member

5 Posts

Posted - 2004-12-24 : 12:46:21

Thanks nr and Andrew...I think we're almost there.
nr, I was able to get this to work:


"SELECT D.dept_id, D.name, T.lineage, T.depth FROM k_depts D " & _
"INNER JOIN k_dept_tree T ON D.dept_id = T.dept_id " &_
"WHERE EXISTS (SELECT T.dept_id FROM k_dept_tree T WHERE T.dept_id = D.dept_id AND T.Lineage Like '%" & parentDept_id & "%') " & _
"ORDER BY T.lineage + convert(varchar(4),D.dept_id,4)"

Which produced these results:

However, the "right('' + " part of the code you suggested gave me the error: "The right function requires two arguements". What am I doing wrong???
I truly appreciate your time. Thanks!

Oh, and node is T.node for me I believe.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-24 : 12:50:53

Missing a paran.

ORDER BY T.lineage + right(' ' + (convert(varchar(4),D.dept_id)),4)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-24 : 15:51:17

Or - what I meant was

ORDER BY T.lineage + right(' ' + convert(varchar(4),D.dept_id),4)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jyaki
Starting Member

5 Posts

Posted - 2004-12-30 : 17:25:37

Thank you SOOO much guys! It all worked perfectly. Sorry for the delay in repsonding back. I took some time off of work.
I have one more question if you don't mind. Is there an easy way to output my data into an XML format?


"SELECT D.dept_id, D.name, T.lineage, T.depth FROM k_depts D " & _
"INNER JOIN k_dept_tree T ON D.dept_id = T.dept_id " &_
"WHERE EXISTS (SELECT T.dept_id FROM k_dept_tree T WHERE T.dept_id = D.dept_id AND T.Lineage Like '%" & parentDept_id & "%') " & _
"ORDER BY T.lineage + Ltrim(Str(T.Node,6,0))"

Thanks again for all your help! :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-30 : 17:41:32

use
select ....
order by ...
for xml [RAW | AUTO | EXPLICIT]

another way is to put that into a dataset and use it's WriteXml() function.

Go with the flow & have fun! Else fight the flow

jyaki
Starting Member

5 Posts

Posted - 2004-12-30 : 18:10:41

Can I still use GetRows, or how do I output it after the SELECT statement?
Thanks! :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-30 : 18:22:07

emmm.... GetRows() is ado function not ado.net, no?
WriteXml() is ado.net function.

output it where?
for xml returns the select in form of xml. you'll need to decide which one suits you best.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources