Solved

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

Posted on 2007-12-04
6
1,655 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
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.

635 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