--- In firebird-support@yahoogroups.com, "robert_difalco" wrote:

> I think you are getting caught up in details that are not germane to
> my question. So let me ask the question again with an example.
>
> Say I have tree structure where leaves can be linked (i.e. present)
> in more than one group at a time.
>
> I have a distinct query like this:
>
> SELECT DISTINCT G.oid, G.name, G.description, G.type [, ...]
> FROM Groupable G
> JOIN Link ON G.oid = Link.childId
> JOIN Path ON Link.parentId = Path.descendantId
> WHERE (G.type = LEAF AND Path.ancestorId = 12345)
>
> To return all the Groupable leaves that descend from the group with
> the unique identity of 12345.
>
> The name and description fields are sizable VARCHAR fields and as a
> result greately slow down the query because of the DISTINCT.
>
> I can make the query faster like so:
>
> SELECT G.oid, G.name, G.description, G.type [, ...]
> FROM Groupable G
> WHERE G.oid IN (
> SELECT G.oid
> FROM Groupable G
> JOIN Link ON G.oid = Link.childId
> JOIN Path ON Link.parentId = Path.descendantId
> WHERE (G.type = LEAF AND Path.ancestorId = 12345))
>
> I have found that most of the DISTINCT queries in my application are
> only interested in being distinct on a SINGLE field (in this case
> G.oid).
>
> Is there a better (more general) way to achieve this result than
> what I have shown in the second version of the Tree query? Or am I
> already taking the best approach?
>
> R.

Sorry for the long delay in answering, I haven't been online for a few
days.

I think your solution seems almost OK, but just almost. You really
shouldn't have two aliases with the same name, so remove Groupable
from your subquery:

SELECT G.oid, G.name, G.description, G.type [, ...]
FROM Groupable G
WHERE G.type = LEAF AND
G.oid IN (
SELECT L.childId
FROM Link L
JOIN Path P ON L.parentId = P.descendantId
WHERE P.ancestorId = 12345)

is the query you should have been writing. Myself, I'd write this:

SELECT G.oid, G.name, G.description, G.type [, ...]
FROM Groupable G
WHERE G.type = LEAF AND
EXISTS(SELECT *
FROM Link L
JOIN Path P ON L.parentId = P.descendantId
WHERE P.ancestorId = 12345
AND G.oid = L.childId)

However, I think Firebird 1.5 and later automatically changes the
former query into the latter, so they should be almost identically quick.

HTH,
Set