SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 recursive query problem		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tpiazza55
Posting Yak Master

162 Posts
Posted - 08/30/2007 :  09:42:07  Show Profile  Reply with Quote
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

Sweden
29156 Posts
Posted - 08/30/2007 :  09:42:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The anchor point (query above UNION ALL) needs a WHERE.

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 08/30/2007 :  09:45:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
;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


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts
Posted - 08/30/2007 :  09:47:00  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 08/30/2007 :  09:50:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts
Posted - 08/30/2007 :  09:53:34  Show Profile  Reply with Quote
WHERE <>

gives me a syntax error
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts
Posted - 08/30/2007 :  10:05:09  Show Profile  Reply with Quote

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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 08/30/2007 :  10:08:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
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

E 12°55'05.25"
N 56°04'39.16"
Edited by - SwePeso on 08/30/2007 10:18:03
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 08/30/2007 :  10:08:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts
Posted - 08/30/2007 :  10:20:47  Show Profile  Reply with Quote
i know about the where statement -- thought about that right after i clicked it
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts
Posted - 08/30/2007 :  10:42:33  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 08/30/2007 :  11:03:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

tpiazza55
Posting Yak Master

162 Posts
Posted - 08/30/2007 :  11:27:01  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 08/30/2007 :  11:47:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 08/30/2007 :  11:53:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 08/30/2007 :  11:59:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Edited by - SwePeso on 08/30/2007 12:00:07
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000