ordering problem

[ryan.mclean]

Hi everyone, I have a database (not designed by me) in sql server, I am
having trouble ordering it correctly, and I would appreciate any help.

Here is my structure, it's sort of hierarchical:

TABLE_A
ID_COL
SUB_ID_COL

TABLE_B
ID_COL'
NAME

This could be thought of as an employee and supervisor structure. We
have employees (TABLE_B) and their relationship (TABLE_A).

I know I need to use a group and oder by, but I am drawing blanks
today. I need the result to be (if using the employee example):

Bob
-Ryan
-Sue

Mark

Nick
-George
-Paul
--Ringo
--Star

Here is my half-hearted stab at it:

SELECT *
FROM TABLE_A A
INNER JOIN TABLE_B B
GROUP BY A.SUB_ID_COL
ORDER BY NAME;

Thank you very much for any help you can offer.

Have a great night!
Ryan

[ryan.mclean]

Wow, my stab is way off. Hmm, this one is closer:

SELECT B1.NAME_COL, B2.NAME_COL
FROM TABLE_A A INNER JOIN
TABLE_B B1 ON A.ID_COL = B1.ID_COL INNER JOIN
TABLE_B B2 ON A.SUB_ID_COL = B2.ID_COL
GROUP BY A.SUB_ID_COL, A.ID_COL, B1.NAME_COL, B2.NAME_COL;

There must a be a better way of doing this, I just can't think right
now :)

Thanks again,
Ryan

[Robert Klemme]

ry*********@gmail.com wrote:

Wow, my stab is way off. Hmm, this one is closer:

SELECT B1.NAME_COL, B2.NAME_COL
FROM TABLE_A A INNER JOIN
TABLE_B B1 ON A.ID_COL = B1.ID_COL INNER JOIN
TABLE_B B2 ON A.SUB_ID_COL = B2.ID_COL
GROUP BY A.SUB_ID_COL, A.ID_COL, B1.NAME_COL, B2.NAME_COL;

There must a be a better way of doing this, I just can't think right
now :)

Problem is that you need some form of recursion. This can't be done with
standard SQL and CONNECT BY PRIOR exists just in Oracle.

The alternative (that works with SQL Server) is to store the tree
differently. Basically you do an in order tree traversal and store the
counts on node entry and exit. You get something like

create table tree (
no_left int,
no_right int,
node_id int
)

Insert and delete operations will have to properly update these. It's not
overly complicated and not very inefficient.

Kind regards

robert

[--CELKO--]

Look up the nested sets model in this newsgroup or get a copy of my
book TREES & HIERARCHIES IN SQL. You can move teh parts of the tree
strucutre about as you wish and get any sort4ed order without recursion
or proprietary nightmares like CONNECT BY.

[ryan.mclean]

Thank you for the replys!

Have a great day!

Ryan

--CELKO-- wrote:

Look up the nested sets model in this newsgroup or get a copy of my
book TREES & HIERARCHIES IN SQL. You can move teh parts of the tree
strucutre about as you wish and get any sort4ed order without recursion
or proprietary nightmares like CONNECT BY.