Solved

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

Posted on 2007-12-04
6
1,652 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select statement with COUNT (SQL Server) and return records that have 0 count as well 13 24
SSMS Opening Mode 9 20
SQL Log size 3 20
Pivot tables in SQL 1 20
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

821 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