Solved

SQL SERVER 2000 - Nested Select - TreeView (Using ParentID)

Posted on 2007-12-04
6
1,649 Views
Last Modified: 2008-02-01
Experts,
I have got a table that has the following in CSV form:

-------------------------------------
ID,CategoryName,ParentID
1,Software,
2,Progamming,1
3,Office,1
4,ASP,2
5,ASP.NET,2
6,PHP,2
7,VB.NET,2
8,C#,2
9,Excel,3
10,Word,3
-------------------------------------

How do i write a select statement that selects all that falls under "Software", subs and sub of subs, no matter how many nested levels get created under the main root in the future.
0
Comment
Question by:feesu
  • 3
  • 3
6 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20402216
You can't do that in a single select. Like you say "no matter how many levels", which could be 1000!

here's an option though.  If you want to select everything to do with "Software", run

exec nestedSelect "Software"
create proc nestedSelect( @CategoryName varchar(50) )
as
select ID,CategoryName,ParentID
into #tmp
from myTable
where CategoryName = @CategoryName
 
while @@rowcount > 0
  insert into #tmp
  select a.ID,a.CategoryName,a.ParentID
  from myTable a
    inner join #tmp b on a.ParentID = b.ID
    left join #tmp c on a.ID = c.ID
  where c.ID is null
 
select * from #tmp
GO

Open in new window

0
 

Author Comment

by:feesu
ID: 20409826
imitchie,
After executing this SP i got the following error:

Server: Msg 8101, Level 16, State 1, Line 9
An explicit value for the identity column in table '#tmp' can only be specified when a column list is used and IDENTITY_INSERT is ON.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
ID: 20409840
Didn't realise you had an identity column.
Try this instead

create proc nestedSelect( @CategoryName varchar(50) )
as
 
create table #tmp ( ID int, CategoryName varchar(50), ParentID int )
 
insert into #tmp
select ID,CategoryName,ParentID
from myTable
where CategoryName = @CategoryName
 
while @@rowcount > 0
  insert into #tmp
  select a.ID,a.CategoryName,a.ParentID
  from myTable a
    inner join #tmp b on a.ParentID = b.ID
    left join #tmp c on a.ID = c.ID
  where c.ID is null
 
select * from #tmp
GO

Open in new window

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:feesu
ID: 20409924
Still, got the following:

Server: Msg 8101, Level 16, State 1, Line 6
An explicit value for the identity column in table '#tmp' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 12
An explicit value for the identity column in table '#tmp' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Please look at the code I am trying to execute:
create table #tmp ( ikey int, CategoryName varchar(50), ParentID int )
 
DECLARE @Category_IKey INT
SET @Category_IKey = 48
 
INSERT INTO #tmp
SELECT IKEY,CategoryName,ParentID
FROM  FAQ_Categories
WHERE IKey = @Category_IKey
 
WHILE @@rowcount > 0
  INSERT INTO #tmp
  SELECT a.IKey,a.CategoryName,a.ParentID
  FROM FAQ_Categories a
    INNER JOIN #tmp b on a.ParentID = b.IKey
    LEFT JOIN #tmp c on a.IKey = c.IKey
  WHERE c.IKey IS NULL
 
SELECT * FROM #tmp

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20409946
which is not in an SP...
drop the table first, I think the previous run still had the table
add this to the top

drop table #tmp
GO
0
 

Author Comment

by:feesu
ID: 20409982
Thanks alot!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question