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

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

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

Go to Top of Page

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

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-08-30 : 09:53:34
WHERE <>

gives me a syntax error
Go to Top of Page

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

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

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

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

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

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

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


Go to Top of Page

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

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

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

- Advertisement -