Recursive Select? 1

[Brandan34]

I am not sure that is the right topic name, it is as best as I can describe it though.

I have one table, each record describes a cow and has a whole lot of data on each one. There are only three have a berring on my question.

UID is a unique number used to identify each cow.
Dam, is the UID of another cow who is this cows mother.
Sire, is the UID of another cow who is this cows father.

A few sample records would look like

UID--|Dam-|Sire-|---other data
1----|NULL|NULL-|---other columns //First cow ever
2----|NULL|NULL-|---other columns //First bull ever
3----|1---|2-----|---other columns //First calf
4----|1---|2-----|---other columns //Second calf
5----|3---|2-----|---other columns //First calf out of the second generation

Repeat 7 thousand times starting with 200 different cows and 50 different bulls will get the idea. In short think of it as a big family tree and you will have the right idea.

There are two things that I want to do and can do when I use some C++, but I have no clue how to do with pure SQL.

#1. recursive select I want to pick a particular cow and return data on her calf, then their calves, and their calves till I have gotten the data that I have asked for on all of the first cows descendants.

In short I want a recursive select that will take data from a returned record return that data to me and also put some of it(in this case the UID) into the same select and keep asking that question till there are no new answers.

#2. calculate data from 2 records same tableThe second thing I want to do is calculate some numbers where I need some data from both the cow and her calf. If the cow and the calf where in two different tables I know I could do it, as they are in the same table I do not know how to form the SQL statement.


If someone knows where there are viewalbe examples of of code doing ether of these on an existing database(a family tree program would likely have it) would great. If not I am not asking people to do my work for me, a link to a document that talks about this or a few suggestions that point me in the right direction would make me happy.

 

[PHV]

#2
SELECT C.*, M.*, F.*
FROM yourTable AS C
INNER JOIN yourTable AS M ON C.Dam = M.UID
INNER JOIN yourTable AS F ON C.Sire = F.UID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Brandan34]

Thanks, a join(I did not know they could be used on the same table) with aliases solves problem #2 and is something I will put to good use in a lot of different problems.

It also sujects a possible way to solve problem #1

SELECT A.UID, A.dam
From cow A, cow B
Where A.dam=B.UID

Cow A is the calf, Cow B is the mother.
That would show me every cow in the database who has a mother. If I change B.UID to the UID of the mother I can find all of her calves and no others(I can also do that with a normal select and where clause). Any ideas how it could be setup so that when it finds a record B.UID would get replaced with A.UID and the new SQL statement gets run(is this even possible with pure SQL)?

 

[simian101]

Just thinking out loud here... and some of the other guys would probably have a better idea. But you could probably use a cursor (most dba's discourge their use).

There should be plenty of examples for you db.

Simi

 

[LKBrwnDBA]

If you use Oracle it would be a simple 'hierarchical" query.

Otherwise this is a T-SQL example I swiped from the web:

DECLARE @cSearch char(50)
        SET @cSearch = 'Cow1'

        ; WITH CowCTE (UID, Dam, Sire) AS 
            -- Anchor query
            (SELECT UID, Dam, Sire, OtherData 
                    FROM @CowsTable
                    WHERE UID = @cSearch 
                UNION ALL
                    -- Recursive query
                    SELECT C.UID, C.Dam, C.Sire, C.OtherData 
                      FROM @CowsTable C 
                            INNER JOIN CowCTE
                            ON CowCTE.UID C.Dam )         
        SELECT * FROM CowCTE

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb