Please help me understand the query

Question

Please help me understand the query

saurabh.kumar

SSC Enthusiast

Points: 151
More actions
June 27, 2011 at 11:28 am

#96411

-- 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

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. Selburg

saurabh.kumar SSC Enthusiast Points: 151 More actions · Answer 3

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.

GSquared SSC Guru Points: 260824 More actions · Answer 4

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