Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Recursive Select?

Recursive Select?

Recursive Select?

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.  

RE: Recursive Select?

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

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)?  

RE: Recursive Select?

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.


RE: Recursive Select?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close