Ive never been good with relationships

I'm having some trouble working out how to query some data. Rather than explain up front, here's some examples of what I want to achieve:

*******************************************************

I've got a structure which looks vaguely like this:

[ANCESTORS]

Grandparent
Parent
Child

If limit by grandparents, then I only get the lineage for that particular grandparent. I.e.:

SELECT *
FROM [ANCESTORS]
 (some sortof joins here )
Where Grandparent.Name ='Cybill'

This would return all of the children of 'Cybill' and their children. Now, if I use the following query:

SELECT *
FROM [ANCESTORS]
 (some sortof joins here )
Where Child.Name ='Jean'

This would return Jean's parents + the parents of Jean's parents (Jean's grandparents).

Likewise, if I enter:

SELECT *
FROM [ANCESTORS]
 (some sortof joins here )
Where Parent.Name ='Ron'

Then I would get Ron's parents and also his children.

*******************************************************

So, as you can see, at first it appears that I'm after a LEFT JOIN - meaning that the grandparents don't need to have child records to be returned, but, then it turns out that I need INNER JOINS - to limit grandparents when I choose children.

Can anybody see my dilemma here?

Mark-up ASP.net posts here
MarkItUp.com... no. I don't. I've done something like this before with someone, and we didn't go about that method. :(

What's the problem with doing inner joins?|||Because, with Inner JOINs I am relying upon the existance of children to be able to return the grandparents.|||I actually have one table "Ancestors" and also a separate linking table which contains the relationships:

[Ancestors] ( id int, Name varchar )
[Relationships] ( id, fkid )

What I need to be able to do is to write a single query which can span "up to 7" lines of descendency, that is:

Great-Great-Great-Grandparent
Great-Great-Grandparent
Great-Great-Grandparent
Great-Grandparent
Grandparent
Parent
Child

I just have absolutely no idea how to write a single sql query which could filter on one or more levels but ensure referential integrity down the line. In other words, I can specify a child which would trace up the tree in a single line, or, specify a Great-Great-Great-Grandparent which would span out from a single point and would show all the way down to leaf nodes regardless of which level they finished at.|||Can we use a UDF? :-)|||Yes, I'm using SqlServer 2K.

I have finally tuned the stored procedure "program" enough to get my - previously 14 hour (give or take 100 milliseconds) - query down to sub-20 seconds. This is good enough to ship to the client so, I'm going to go with what I have for now.|||20 seconds? I'm still not believing that to be good enough. :) but if it's good enough for the client, it's good enough I guess.

If you're using any temp tables or what not, try indexing them before you apply data to them. I trimmed a procedure that executed in 12 seconds to 2.|||KraGiE, I agree, 20 seconds is a long time to wait, but, it's working so that's a much better position than I had 2 days ago.

I do have # temp tables, about 12 of them actually (more on that later), and my final optimization was to add indexes and optimization hints in the appropriate places, this reduced a 2 minute 20 second query to sub 30 seconds.

As for the 12 temp tables, this, to me was a fair indicator that the schema of the database was wrong to begin with so now, I 'm actually pushing to have the db schema altered too. I'm hoping that this will give me enough of a foothold to be able to achieve the necessary remaining improvements|||Personally, I'm a big fan of int based look up tables. :) Well, in cases like this, I'm fond of them because you can branch them out as far as you want without having just 'add a new field'.

I also think loops are my best friend when used properly.

What I meant by the indexes (if this is what you're doing already, then I'm a redundant moron) is ...

SET NOCOUNT ON
CREATE TABLE #TempTable
(
 tableID int IDENTITY,
 TableField varchar(50)
 SomeOtherVar int
)
GO
CREATE INDEX idx_Temp
 ON #TempTable ( tableID )
GO
INSERT INTO #TempTable
SELECT
 TableID,
 TableField,
 SomeOtherVar
FROM
 RealTable
-- WHERE Your Criteria
GO
-- Do Other Calculations
TRUNCATE TABLE #TempTable
DROP INDEX idx_Temp
DROP TABLE #TempTable
GO
-- Yes Terri, I space my sql in procedures and QA this way normally, and I'm just lazy when I post here. :)

|||Yeh, that's pretty much what I'm doing already re: the indexes ( although who am I to call you a redundant moron :P ) except that I create the index only after population and, therefore also set the fill factor to 100% - but, for the purposes of an illustration, yes, that's pretty much what I do.|||awesome. In some cases, I'll put the index in before I start populating if the data's going to be enormous. It sets a bit faster from my experience.