×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Select Directory?

Select Directory?

Select Directory?

(OP)
I have a table (Directory) set up with these columns:

ID, Name, ParentID

Where the ParentID points to the ID of the Directory Name above it.

I can get the Parent Directory with Select Name from Directory where ID=(ParentID value).  What I want to do is create a select statement that will return all of the directories above the current one.  Is this possible?

RE: Select Directory?

Darkman,

Hopefully I can return the favour.  By the looks of things you are doing an inner join on the two tables, yet this query only returns values which are populated in the parent and child tables.  But you want it to return the values whether they are populated in both tables or not.  Am I correct?

If so try using outer joins instead of innner joins.  Try either right outer join or left outer join to get your desired result.

Hope this helps,

Ray.

RE: Select Directory?

(OP)
Thanks, but that's not quite what I'm trying to do...  Let me fill in this table a bit so you can see what I'm talking about (it's kind of hard to explain this otherwise....)

   ID           Name    ParentID
-----   -------------   --------
    1           World          0
    2          Europe          1
    3         England          2
    4          London          3
    5   North America          1
    6   United States          5
    7      California          6
    8     Los Angeles          7
    9      Sacramento          7
   10   San Francisco          7
   11         Florida          6
   12           Tampa         11

From this table, Los Angeles "belongs" to California, which "belongs" to United States, etc.

What I need is a query that will, if I have Los Angeles' ID, give me California, United States, North America, and World...

I can get the right result by doing queries until I hit the top (ParentID=0), but I'd rather do it in one query if possible...  Any ideas?

RE: Select Directory?

Dear Darkman,
I had similar requirement some time back. Just I modified it to suit your need. I am sorry it's not a single SQL stmt.

-------------------------
CREATE PROC TravelToRoot
@Child_ID INT
AS
BEGIN
CREATE TABLE #tmp_sort1
(
  pid       INT         NULL                
, id INT NULL
, sl_no INT IDENTITY(1, 1)
)


DECLARE @id INT
, @pid INT

SELECT @id = 7 -- Enter your child id number
WHILE ( 1 = 1)
BEGIN
SELECT @pid = @id

SELECT @id = pid
FROM directory  
WHERE id = @id

IF @@ROWCOUNT = 0
BREAK

INSERT INTO #tmp_sort1 ( pid, id)
SELECT @pid, @id
END

SELECT T.pid, T.id, D.NAME
FROM #tmp_sort1 t
, directory d
WHERE T.ID = D.ID
ORDER BY sl_no desc
END

RE: Select Directory?

(OP)
Excellent!  That's exactly what I was looking for.  Thanks :-)

DarkMan

RE: Select Directory?

You can't do them all easily, because you need to use recursion to traverse the tree.

you can do this, to return a set number (2 here) of parents.

select *
from Directory d1
 left join Directory d2
  left join Directory d3
  on d3.Id = d2.ParentId
 on d2.Id = d1.ParentId
where d1.ParentId IN NOT NULL
ORDER BY d1.Id, d2.Id, d3.Id


(I assume that records without parents have a NULL ParentId)


This returns a recordset that is not normalized, but has all the info you need.

d1.*, d2.*, d3.*

If d2 has a parent, d3.Id IS NOT NULL

If you're using ADO, You could also buils a stored procedure that traverses the tree and returns a recordset for each row, then use the NextRecordset (i think) method.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close