Solved

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

Posted on 2007-12-04
6
1,654 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 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