June 27, 2011 at 11:28 am
-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
SELECT Mother
FROM Person
WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
SELECT Father
FROM Person
WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
SELECT Person.Father
FROM Generation, Person
WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
SELECT Person.Mother
FROM Generation, Person
WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO
I am unable to understand the usage of WITH clause. What is the Generation(ID) refer to? Specifically the ID in the Generation.
June 27, 2011 at 11:34 am
the WITh clause is a newer, fancy way of creating a temporary table...it's valid only for the next statement after the closing parentesis of the WITH;
so it's like saying CREATE TABLE GENERATION (ID INT) INSERT INTO GENERATION ({the SELECT Mother UNION....}
if you needed to access the data more than once, you'd stick it into a #temp table, but that's a simple fast way of doing it...you get to declare the table and the column list in a simple line.
Lowell
June 27, 2011 at 11:46 am
The 'ID' column in the Generation CTE is an Alias for the Mother and Father fields in the source table. You can also write it like this ...
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation AS
(
-- First anchor member returns Bonnie's mother.
SELECT Mother AS ID
FROM Person
WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
SELECT Father
FROM Person
WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member ..........................
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 27, 2011 at 12:01 pm
Jason Selburg (6/27/2011)
The 'ID' column in the Generation CTE is an Alias for the Mother and Father fields in the source table. You can also write it like this ...
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation AS
(
-- First anchor member returns Bonnie's mother.
SELECT Mother AS ID
FROM Person
WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
SELECT Father
FROM Person
WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member ..........................
I know its a bit too much to ask for , but can someone help me understand the last Select query of the above snippet.I am referring to the Select outside the WITH clause's ending.What if my source table does not have an identity column? Say it has a Varchar column that contains WO001 kind of entries. As in 1st column is Work Order Id which is a combination of char and number.
June 27, 2011 at 1:05 pm
It can work with any type of column.
The "with" statement defines a "recursive common table expression" (also called "recursive CTE"), and is used to crawl a hierarchy. If that's what your work orders are, then it can be queried that way.
Can you post the definition for the table you are trying to query?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply