Select Directory?

[DarkMan]

I have a table (Directory) set up with these columns:<br><br>ID, Name, ParentID<br><br>Where the ParentID points to the ID of the Directory Name above it.<br><br>I can get the Parent Directory with Select Name from Directory where ID=(ParentID value).&nbsp;&nbsp;What I want to do is create a select statement that will return <b>all</b> of the directories above the current one.&nbsp;&nbsp;Is this possible?

 

[raybetts]

Darkman,<br><br>Hopefully I can return the favour.&nbsp;&nbsp;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.&nbsp;&nbsp;But you want it to return the values whether they are populated in both tables or not.&nbsp;&nbsp;Am I correct?<br><br>If so try using outer joins instead of innner joins.&nbsp;&nbsp;Try either right outer join or left outer join to get your desired result.<br><br>Hope this helps,<br><br>Ray.

 

[DarkMan]

Thanks, but that's not quite what I'm trying to do...&nbsp;&nbsp;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....)<br><FONT FACE=monospace><br>&nbsp;&nbsp;&nbsp;ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;ParentID<br>-----&nbsp;&nbsp;&nbsp;-------------&nbsp;&nbsp;&nbsp;--------<br>&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;World&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<br>&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Europe&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br>&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;England&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2<br>&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;London&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3<br>&nbsp;&nbsp;&nbsp;&nbsp;5&nbsp;&nbsp;&nbsp;North America&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<br>&nbsp;&nbsp;&nbsp;&nbsp;6&nbsp;&nbsp;&nbsp;United States&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5<br>&nbsp;&nbsp;&nbsp;&nbsp;7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6<br>&nbsp;&nbsp;&nbsp;&nbsp;8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Los Angeles&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7<br>&nbsp;&nbsp;&nbsp;&nbsp;9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sacramento&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7<br>&nbsp;&nbsp;&nbsp;10&nbsp;&nbsp;&nbsp;San Francisco&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7<br>&nbsp;&nbsp;&nbsp;11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Florida&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6<br>&nbsp;&nbsp;&nbsp;12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tampa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11<br></font><br>From this table, Los Angeles &quot;belongs&quot; to California, which &quot;belongs&quot; to United States, etc.<br><br>What I need is a query that will, if I have Los Angeles' ID, give me California, United States, North America, and World...<br><br>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...&nbsp;&nbsp;Any ideas?

 

[Guest_imported]

Dear Darkman,<br>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.<br><br>-------------------------<br>CREATE PROC TravelToRoot<br> @Child_ID INT<br>AS<br>BEGIN<br> CREATE TABLE #tmp_sort1<br> ( <br> &nbsp;&nbsp;pid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NULL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br> , id INT NULL<br> , sl_no INT IDENTITY(1, 1)<br> )<br><br><br> DECLARE @id INT<br> , @pid INT<br><br> SELECT @id = 7 -- Enter your child id number<br> WHILE ( 1 = 1) <br> BEGIN<br> SELECT @pid = @id<br><br> SELECT @id = pid<br> FROM directory&nbsp;&nbsp;<br> WHERE id = @id<br><br> IF @@ROWCOUNT = 0 <br> BREAK<br><br> INSERT INTO #tmp_sort1 ( pid, id)<br> SELECT @pid, @id<br> END<br><br> SELECT T.pid, T.id, D.NAME <br> FROM #tmp_sort1 t<br> , directory d<br> WHERE T.ID = D.ID<br> ORDER BY sl_no desc<br>END

 

[DarkMan]

Excellent!&nbsp;&nbsp;That's exactly what I was looking for.&nbsp;&nbsp;Thanks <br><br>DarkMan

 

[Guest_imported]

You can't do them all easily, because you need to use recursion to traverse the tree.<br><br>you can do this, to return a set number (2 here) of parents.<br><br>select * <br>from Directory d1<br>&nbsp;left join Directory d2 <br>&nbsp;&nbsp;left join Directory d3<br>&nbsp;&nbsp;on d3.Id = d2.ParentId<br>&nbsp;on d2.Id = d1.ParentId<br>where d1.ParentId IN NOT NULL<br>ORDER BY d1.Id, d2.Id, d3.Id<br><br><br>(I assume that records without parents have a NULL ParentId)<br><br><br>This returns a recordset that is not normalized, but has all the info you need.<br><br>d1.*, d2.*, d3.*<br><br>If d2 has a parent, d3.Id IS NOT NULL<br><br>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.