Hi Alec,

> select *
> from
> (
> -- This sub-query returns empty result in 0.047 s
> SELECT * FROM PHYSICAL_COPY WHERE (PHYSICAL_COPY."REPOSITORY_TYPE" = 'blah')
> ) a
> INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" = COPY_CLASSIFICATION."COPY_ID"
> INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY."ID";
>

try to replace first "inner join" with "left join "


Regards,
Zlatko

--- In firebird-support@yahoogroups.com, Alec Swan <alecswan@...> wrote:
>
> We have 3 tables: COPY, PHYSICAL_COPY  and COPY_CLASSIFICATION. The
> last two all linked to COPY using foreign keys: PHYSICAL_COPY.COPY_ID
> -> COPY.ID and COPY_CLASSIFICATION.COPY_ID to COPY.ID
>
> We have the following query which returns an empty result set because
> the sub-query with alias "a" returns an empty result set
>
> select *
> from
> (
>     -- This sub-query returns empty result in 0.047 s
>     SELECT * FROM PHYSICAL_COPY WHERE (PHYSICAL_COPY."REPOSITORY_TYPE" = 'blah')
> ) a
> INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" = COPY_CLASSIFICATION."COPY_ID"
> INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY."ID";
>
> This query with 2 joins takes 0.8 s. However, commenting out either
> join brings this down to 0.047s.
>
> How can we optimize this query so that it runs fast with both joins in place?
>
> Thanks,
>
> Alec
>