Hi again!

I a table doesn't contribute to the result, I'd leave it out. Other
than that, I cannot see how to improve your SELECT - that is unless it
is possible to store the ancestorId in either of Version, Element or
Node (through triggers). I don't even know whether I would have gone
for SELECT DISTINCT or EXISTS in your case, I guess it is the Link
table that makes the DISTINCT necessary?

Set

--- In firebird-support@yahoogroups.com, "robert_difalco" wrote:
> I already see one simplification:
> >
> > SELECT <Version fields>
> > FROM Version V
> > WHERE V.oid IN (
> > SELECT E.currentVersionID
> > FROM Element E
> > JOIN Node N ON E.nodeId = N.oid
> > JOIN Link L ON N.oid = L.childId
> > JOIN Path P ON L.parentId = P.decsendantId
> > WHERE P.ancestorId = 12345 );
> >
> Can be rewritten as:
>
> SELECT <Version fields>
> FROM Version V
> WHERE V.oid IN (
> SELECT E.currentVersionID
> FROM Element E
> JOIN Link L ON E.nodeId = L.childId
> JOIN Path P ON L.parentId = P.decsendantId
> WHERE P.ancestorId = 12345 );
>
> However, while it is simpler, I'm not sure it would perform better
> E.nodeId = L.childId will have to more variations as L.childId will
> include groups if a node is deeply or multiply nested whereas
> E.nodeId = N.oid will only try for actual nodes.
>
> Thoughts?
>
> R.