--- In IBObjects@y..., Raymond Kennington <progsol@c...> wrote:

>
> SELECT ID, Name, Sex, "SIre ID" AS ParentID FROM Animal A1
> WHERE Sex = 'M'
> AND NOT EXISTS (SELECT "Sire ID" FROM Animal A2 WHERE A2."Sire

ID" = A1.ID)

> UNION
> SELECT ID, Name, Sex, "Dam ID" AS PreantID FROM Animal A1
> WHERE Sex = 'F'
> AND NOT EXISTS (SELECT "Dam ID" FROM Animal A2 WHERE A2."Dam

ID" = A1.ID)

As Helen said, you should probably look at the query to see if it
can be optimized.
To eliminate the UNION (which the optimizer will probably gag on),
you should at least do the query in a selectable SP with 2 separate
queries. Like:
procedure sp_XX
returns ID,Name,Sex,ParentID

select ID,Name,Sex,"SireID" ...
into :ID,:Name,:Sex,:ParentID do
begin
suspend;
end

select ID,Name,Sex,"DamID" ...
into :ID,:Name,:Sex,:ParentID do
begin
suspend;
end

//------------------------------

Better though... You could also have an OR in the EXISTS sub-query
to eliminate the double query. Like:
procedure SP_XX
returns ID,Name,Sex,ParentID

select ID,Name,Sex,"SireId","DamID" ...
where NOT EXISTS(select ID from ... where SireID=ID OR DamID=ID)
into :ID,:Name,:Sex,:tSire,:tDam do
begin
if (tDam = 0) then
ParentID = tSire;
else
ParentID = tDam;
suspend;
end;

//-------------------------------------

OR... you could have a child table for Parents which would have
the following DDL:
PKEY
ID
SEX
PARENTID

The query could then be a simple matter of checking if there are
any rows with ID=PARENTID for any animal.

Anyway... Hope this helps. Eric.

P.S. If you haven't already done so, you should download Craig
Stuntz's absolutely fantastic IB PlanAnalyzer. It has helped me a
great deal on my queries, indexes, etc. I can't remember the URL...
just do a google search on IBPlanAnalyzer.