Solved

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

Posted on 2007-12-04
6
1,647 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

920 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now