recursive query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

recursive query problem

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-08-30 : 09:42:07

So I have tblJobItem with fields

JobItemName, JobItemSublevel, JobItemParent, JobItemChild1, JobItemChild2

JobItemName is the name of item name
sublevel goes from 0 - 3

if sublevel is 0 then parent, child1, child2 are null
if sublevel is 1 then child1, child2 are null and parent is value of sublevel 0 jobitemname

if sublevel is 2 then child2 is null and parent is value of sublevel 0 jobitemname and child1 is name of sublevel 1 jobitemname

all that said Im trying to write a recursive query to get he heirachy structure -- the following gives me an error that there is no anchor query for tbljobitem

WITH tblJobItem (JobItemName, JobItemSubLevel, JobItemParentName, JobItemChildName) AS
(
SELECT a.JobItemName, a.JobItemSubLevel, a.JobItemParentName, a.JobItemChildName FROM tblJobItem AS a
UNION ALL

SELECT a.JobItemName, b.JobItemSubLevel, a.JobItemParentName, a.JobItemChildName
FROM tblJobItem AS a

INNER JOIN tblJobItem AS b ON a.jobitemsublevel = b.JobItemSubLevel
)

SELECT *
FROM tbljobitem
ORDER BY JobItemIDID

how do i make this work with the table structure provided.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 09:42:58

The anchor point (query above UNION ALL) needs a WHERE.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 09:45:57

[code];WITH Yak (JobItemName, JobItemSubLevel, JobItemParentName, JobItemChildName)
AS (
SELECT JobItemName,
JobItemSubLevel,
JobItemParentName,
JobItemChildName
FROM tblJobItem
WHERE <>

UNION ALL

SELECT a.JobItemName,
b.JobItemSubLevel,
a.JobItemParentName,
a.JobItemChildName
FROM tblJobItem AS a
INNER JOIN Yak AS b ON a.JobItemSubLevel = b.JobItemSubLevel + 1 -- Could be -1
)

SELECT JobItemName,
JobItemSubLevel,
JobItemParentName,
JobItemChildName
FROM Yak
ORDER BY JobItemIDID[/code]

E 12°55'05.25"
N 56°04'39.16"

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-08-30 : 09:47:00

how would i loop through the entire heirarchy?

it goes sublevel
0 - parent
1 - child
2
2
2
1 - new child
2
0 -- new parent
1 -- new child
2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 09:50:20

Can you post some relevant sample data from your table, so I don't have to randomize something up?
Please post as insert statements with table DDL.

E 12°55'05.25"
N 56°04'39.16"

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-08-30 : 09:53:34

WHERE <>

gives me a syntax error

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-08-30 : 10:05:09

CREATE TABLE JobItemName
(
JobItemID int,
JobItemName varchar(100),
JobItemSubLevel int,
JobItemParentName varchar(100),
JobItemChildName varchar(100),
JobItemChild2Name varchar(100)
)

insert into tbljobitem (jobitemname, jobitemsublevel, jobitemparentname, jobitemchildname, jobitemchild2name)
values('200', '0', '','','')

insert into tbljobitem (jobitemname, jobitemsublevel, jobitemparentname, jobitemchildname, jobitemchild2name)
values('01.000 GENERAL CONDITIONS', '1', '200','','')

insert into tbljobitem (jobitemname, jobitemsublevel, jobitemparentname, jobitemchildname, jobitemchild2name)
values('01.101 GENERAL SUPERINTENDENT', '2', '200','01.000 GENERAL CONDITIONS','')

insert into tbljobitem (jobitemname, jobitemsublevel, jobitemparentname, jobitemchildname, jobitemchild2name)
values('01.101 Equipment', '3', '200','01.000 GENERAL CONDITIONS','01.101 GENERAL SUPERINTENDENT')

insert into tbljobitem (jobitemname, jobitemsublevel, jobitemparentname, jobitemchildname, jobitemchild2name)
values('01.101 Labor', '3', '200','01.000 GENERAL CONDITIONS','01.101 GENERAL SUPERINTENDENT')

insert into tbljobitem (jobitemname, jobitemsublevel, jobitemparentname, jobitemchildname, jobitemchild2name)
values('01.101 Other', '3', '200','01.000 GENERAL CONDITIONS','01.101 GENERAL SUPERINTENDENT')

insert into tbljobitem (jobitemname, jobitemsublevel, jobitemparentname, jobitemchildname, jobitemchild2name)
values('01.102 SUPERINTENDENT', '2', '200','01.000 GENERAL CONDITIONS','')

insert into tbljobitem (jobitemname, jobitemsublevel, jobitemparentname, jobitemchildname, jobitemchild2name)
values('01.102 Equipment', '3', '200','01.000 GENERAL CONDITIONS','01.102 SUPERINTENDENT')

insert into tbljobitem (jobitemname, jobitemsublevel, jobitemparentname, jobitemchildname, jobitemchild2name)
values('01.102 Labor', '3', '200','01.000 GENERAL CONDITIONS','01.102 SUPERINTENDENT')

insert into tbljobitem (jobitemname, jobitemsublevel, jobitemparentname, jobitemchildname, jobitemchild2name)
values('02.000 GENERAL Products', '1', '200','','')

etc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 10:08:06

[code]DECLARE @Sample TABLE (ID INT, ParentID INT, Name VARCHAR(1000))

INSERT @Sample
SELECT 7, NULL, 'Ljunggren' UNION ALL
SELECT 3, 1, 'Gulli' UNION ALL
SELECT 8, 7, 'Kerstin' UNION ALL
SELECT 1, NULL, 'Rosberg' UNION ALL
SELECT 4, 2, 'Peter' UNION ALL
SELECT 5, 3, 'Susanne' UNION ALL
SELECT 2, 1, 'Jan-Eric' UNION ALL
SELECT 10, 9, 'Jennie' UNION ALL
SELECT 6, 3, 'Annelie' UNION ALL
SELECT 9, 7, 'Kenneth' UNION ALL
SELECT 11, 9, 'Jessica'

;WITH Yak (ID, ParentID, Name, Path, Indent)
AS (
SELECT ID,
ParentID,
Name,
CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY Name)),
0
FROM @Sample
WHERE ParentID IS NULL

UNION ALL

SELECT s.ID,
s.ParentID,
s.Name,
CONVERT(VARCHAR, y.Path + ',' + CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY s.Name DESC))),
y.Indent + 1
FROM @Sample AS s
INNER JOIN Yak AS y ON y.ID = s.ParentID
)

SELECT ID,
Name,
ParentID,
Indent
FROM Yak
ORDER BY Path[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 10:08:46

quote:
Originally posted by tpiazza55

WHERE <>

gives me a syntax error

Of course! I told you to put a valid WHERE statement.
I don't know your envionment so you have to put in a little bit of effort yourself.

E 12°55'05.25"
N 56°04'39.16"

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-08-30 : 10:20:47

i know about the where statement -- thought about that right after i clicked it

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-08-30 : 10:42:33

i get an infinite loop on this

WITH Yak (JobItemName, JobItemSubLevel, JobItemParentName, JobItemChildName)
AS (
SELECT JobItemName,
0,
JobItemParentName,
JobItemChildName
FROM tblJobItem
WHERE JobItemParentName is null

UNION ALL

SELECT a.JobItemName,
b.JobItemSubLevel,
a.JobItemParentName,
a.JobItemChildName
FROM tblJobItem AS a
INNER JOIN Yak AS b ON a.JobItemSubLevel = b.JobItemSubLevel + 1
)

SELECT JobItemName,
JobItemSubLevel,
JobItemParentName,
JobItemChildName
FROM Yak

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 11:03:58

Your data is already organized in a hierarchy! You should have seen this.
All you have to do is some advanced ORDER BY.

-- Prepare sample data
DECLARE	@Job TABLE
	(
		JobItemID INT,
		JobItemName VARCHAR(100), 
		JobItemSubLevel INT, 
		JobItemParentName VARCHAR(100), 
		JobItemChildName VARCHAR(100), 
		JobItemChild2Name VARCHAR(100)
	)

INSERT	@Job
	(
		JobItemName,
		JobItemParentName,
		JobItemSubLevel, 
		JobItemChildName,
		JobItemChild2Name
	)
SELECT	'200', '0', '', '', '' UNION ALL
SELECT	'01.000 GENERAL CONDITIONS', '1', '200', '', '' UNION ALL
SELECT	'01.101 GENERAL SUPERINTENDENT', '2', '200', '01.000 GENERAL CONDITIONS', '' UNION ALL
SELECT	'01.101 Equipment', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.101 Labor', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.101 Other', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.102 SUPERINTENDENT', '2', '200', '01.000 GENERAL CONDITIONS', '' UNION ALL
SELECT	'01.102 Equipment', '3', '200', '01.000 GENERAL CONDITIONS', '01.102 SUPERINTENDENT' UNION ALL
SELECT	'01.102 Labor', '3', '200', '01.000 GENERAL CONDITIONS', '01.102 SUPERINTENDENT' UNION ALL
SELECT	'02.000 GENERAL Products', '1', '200', '', ''

-- Show the expexted output
SELECT		JobItemID,
		JobItemName,
		JobItemParentName,
		JobItemSubLevel, 
		JobItemChildName,
		JobItemChild2Name
FROM		@Job
ORDER BY	CASE
			WHEN JobItemSubLevel = 0 THEN CAST(JobItemName AS INT)
			ELSE JobItemSubLevel
		END,
		CASE
			WHEN JobItemSubLevel = 0 THEN 0
			ELSE 1
		END,
		JobItemName

E 12°55'05.25"
N 56°04'39.16"

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-08-30 : 11:27:01

i know this is heirarchial -- i have to join it on a project and compare the jobitems and get those 2 tables linked with a recursive query

just trying to get a handle on how it works

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 11:47:59

I can see that, but your data DOES NOT NEED recursive query!
You are limited to three levels only due to table design.

DECLARE	@Job TABLE
	(
		JobItemID INT,
		JobItemName VARCHAR(100), 
		JobItemSubLevel INT, 
		JobItemParentName VARCHAR(100), 
		JobItemChildName VARCHAR(100), 
		JobItemChild2Name VARCHAR(100)
	)

INSERT	@Job
	(
		JobItemName,
		JobItemSubLevel, 
		JobItemParentName,
		JobItemChildName,
		JobItemChild2Name
	)
SELECT	'200', '0', '', '', '' UNION ALL
SELECT	'01.000 GENERAL CONDITIONS', '1', '200', '', '' UNION ALL
SELECT	'01.101 GENERAL SUPERINTENDENT', '2', '200', '01.000 GENERAL CONDITIONS', '' UNION ALL
SELECT	'01.101 Equipment', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.101 Labor', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.101 Other', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.102 SUPERINTENDENT', '2', '200', '01.000 GENERAL CONDITIONS', '' UNION ALL
SELECT	'01.102 Equipment', '3', '200', '01.000 GENERAL CONDITIONS', '01.102 SUPERINTENDENT' UNION ALL
SELECT	'01.102 Labor', '3', '200', '01.000 GENERAL CONDITIONS', '01.102 SUPERINTENDENT' UNION ALL
SELECT	'02.000 GENERAL Products', '1', '200', '', ''

SELECT		JobItemID,
		JobItemName,
		JobItemParentName,
		JobItemSubLevel, 
		JobItemChildName,
		JobItemChild2Name
FROM		@Job
ORDER BY	CASE
			WHEN JobItemSubLevel = 0 THEN JobItemParentName
			ELSE JobItemName
		END,
		CASE
			WHEN JobItemSubLevel = 0 THEN 0
			ELSE 1
		END,
		JobItemName

If you want to learn recursive CTE, use the example I posted above.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 11:53:36

Here is a recursive CTE for your data above. Please notice that the output is crazier to handle!
You need to distinct the data.

And you still have to sort ORDER BY exactly as before in my posts above.

Do you see? It is how your data is stored that matters

DECLARE	@Job TABLE
	(
		JobItemID INT,
		JobItemName VARCHAR(100), 
		JobItemSubLevel INT, 
		JobItemParentName VARCHAR(100), 
		JobItemChildName VARCHAR(100), 
		JobItemChild2Name VARCHAR(100)
	)

INSERT	@Job
	(
		JobItemName,
		JobItemSubLevel, 
		JobItemParentName,
		JobItemChildName,
		JobItemChild2Name
	)
SELECT	'200', '0', '', '', '' UNION ALL
SELECT	'01.000 GENERAL CONDITIONS', '1', '200', '', '' UNION ALL
SELECT	'01.101 GENERAL SUPERINTENDENT', '2', '200', '01.000 GENERAL CONDITIONS', '' UNION ALL
SELECT	'01.101 Equipment', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.101 Labor', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.101 Other', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.102 SUPERINTENDENT', '2', '200', '01.000 GENERAL CONDITIONS', '' UNION ALL
SELECT	'01.102 Equipment', '3', '200', '01.000 GENERAL CONDITIONS', '01.102 SUPERINTENDENT' UNION ALL
SELECT	'01.102 Labor', '3', '200', '01.000 GENERAL CONDITIONS', '01.102 SUPERINTENDENT' UNION ALL
SELECT	'02.000 GENERAL Products', '1', '200', '', ''

;WITH Yak (JobItemID, JobItemName, JobItemParentName, JobItemSubLevel, JobItemChildName, JobItemChild2Name)
AS (
	SELECT JobItemID, JobItemName, JobItemParentName, JobItemSubLevel, JobItemChildName, JobItemChild2Name
	FROM	@Job
	WHERE	JobItemSubLevel = 0

	UNION ALL

	SELECT		j.JobItemID, j.JobItemName, j.JobItemParentName, j.JobItemSubLevel, j.JobItemChildName, j.JobItemChild2Name
	FROM		@Job AS j
	INNER JOIN	Yak AS y ON y.JobItemSubLevel = j.JobItemSubLevel - 1
)

SELECT JobItemID, JobItemName, JobItemParentName, JobItemSubLevel, JobItemChildName, JobItemChild2Name
FROM (
SELECT DISTINCT JobItemID, JobItemName, JobItemParentName, JobItemSubLevel, JobItemChildName, JobItemChild2Name
FROM Yak
) AS peso
ORDER BY	CASE
			WHEN JobItemSubLevel = 0 THEN JobItemParentName
			ELSE JobItemName
		END,
		CASE
			WHEN JobItemSubLevel = 0 THEN 0
			ELSE 1
		END,
		JobItemName

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 11:59:53

Here is a way to remove the DISTINCT part. But beware of the WHERE clause in the recursive part of the CTE!
Do you now see how your data is stored?

DECLARE	@Job TABLE
	(
		JobItemID INT,
		JobItemName VARCHAR(100), 
		JobItemSubLevel INT, 
		JobItemParentName VARCHAR(100), 
		JobItemChildName VARCHAR(100), 
		JobItemChild2Name VARCHAR(100)
	)

INSERT	@Job
	(
		JobItemName,
		JobItemSubLevel, 
		JobItemParentName,
		JobItemChildName,
		JobItemChild2Name
	)
SELECT	'200', '0', '', '', '' UNION ALL
SELECT	'01.000 GENERAL CONDITIONS', '1', '200', '', '' UNION ALL
SELECT	'01.101 GENERAL SUPERINTENDENT', '2', '200', '01.000 GENERAL CONDITIONS', '' UNION ALL
SELECT	'01.101 Equipment', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.101 Labor', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.101 Other', '3', '200', '01.000 GENERAL CONDITIONS', '01.101 GENERAL SUPERINTENDENT' UNION ALL
SELECT	'01.102 SUPERINTENDENT', '2', '200', '01.000 GENERAL CONDITIONS', '' UNION ALL
SELECT	'01.102 Equipment', '3', '200', '01.000 GENERAL CONDITIONS', '01.102 SUPERINTENDENT' UNION ALL
SELECT	'01.102 Labor', '3', '200', '01.000 GENERAL CONDITIONS', '01.102 SUPERINTENDENT' UNION ALL
SELECT	'02.000 GENERAL Products', '1', '200', '', ''

select * from @job

;WITH Yak (JobItemID, JobItemName, JobItemParentName, JobItemSubLevel, JobItemChildName, JobItemChild2Name)
AS (
	SELECT JobItemID, JobItemName, JobItemParentName, JobItemSubLevel, JobItemChildName, JobItemChild2Name
	FROM	@Job
	WHERE	JobItemSubLevel = 0

	UNION ALL

	SELECT		j.JobItemID, j.JobItemName, j.JobItemParentName, j.JobItemSubLevel, j.JobItemChildName, j.JobItemChild2Name
	FROM		@Job AS j
	INNER JOIN	Yak AS y ON y.JobItemSubLevel = j.JobItemSubLevel - 1
	where		j.JobItemSubLevel = 1
			or (j.jobitemchildname = y.jobitemname and j.JobItemSubLevel = 2)
			or (j.jobitemchild2name = y.jobitemname and j.JobItemSubLevel = 3)
)

SELECT  JobItemID, JobItemName, JobItemParentName, JobItemSubLevel, JobItemChildName, JobItemChild2Name
FROM Yak
ORDER BY	CASE
			WHEN JobItemSubLevel = 0 THEN JobItemParentName
			ELSE JobItemName
		END,
		CASE
			WHEN JobItemSubLevel = 0 THEN 0
			ELSE 1
		END,
		JobItemName

and you still need the ugly sort thing...

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources