Hi Arno,

Many thanks for the reply.

What would be the preference in terms of the two metadata statements
from my first post?

Also BTW - below is an example of the actual SQL generated by the app:

SELECT DISTINCT c.contid, c.surname, c.forename, c.dob, c.postcode,
c.filed, c.available, c.email
FROM cont c
JOIN contskill t1 ON (c.contid=t1.contid) AND (t1.skillid=29 )
JOIN contskill t2 ON (c.contid=t2.contid) AND (t2.skillid=34 )
JOIN contskill t3 ON (c.contid=t3.contid) AND (t3.skillid=247 )
JOIN contskill t4 ON (c.contid=t4.contid) AND (t4.skillid=256 )
JOIN contskill t5 ON (c.contid=t5.contid) AND (t5.skillid=321 )
JOIN contskill t6 ON (c.contid=t6.contid) AND (t6.skillid=3024 )
JOIN contskill t7 ON (c.contid=t7.contid) AND
(
(t7.skillid+0 IN (118,136,784,1789,2184,2393) ) OR
(t7.skillid=1402 AND t7.rating=1 )
)
WHERE ((c.contid>0) AND ( c.FORENAME LIKE "%l%" ) )

Notice in the OR case (t7) I have to add t7.skillid+0 and also some
situations need the (c.contid>0) to help it along. Sad to say but it's
a bit frustrating to have to tweak in this way...Does FB2 fix this?

Tom

--- In firebird-support@yahoogroups.com, "Arno Brinkman"
<fbsupport@...> wrote:

>
> Hi,
>
> At least remove the distinct from the EXISTS predicate.
>
> AFAIU your query you could also merge the exists into the "base" from.
> Or move "contskill t1" also to the EXISTS and the DISTINCT can be

removed at both places.

>
> SELECT DISTINCT
> c.contid, c.surname, c.forename, c.dob,
> c.postcode, c.filed, c.available, c.email
> FROM
> cont c
> JOIN contskill t1 ON (t1.contid = c.contid AND t1.skillid = 10)
> JOIN contskill t2 ON (t2.contid = c.contid AND t2.skillid = 17)
> JOIN contskill t3 ON (t3.contid = c.contid AND t3.skillid = 18)
> JOIN contskill t4 ON (t4.contid = c.contid AND t4.skillid = 22)
> JOIN contskill t5 ON (t5.contid = c.contid AND t5.skillid = 24)
> JOIN contskill t6 ON (t6.contid = c.contid AND t6.skillid = 29)
> JOIN contskill t7 ON (t7.contid = c.contid AND t7.skillid = 30)
> JOIN contskill t8 ON (t8.contid = c.contid AND t8.skillid = 34)
> JOIN contskill t9 ON (t9.contid = c.contid AND t9.skillid = 4209)
> JOIN contskill t10 ON (t10.contid = c.contid AND t10.skillid = 62)
>
>
> SELECT
> c.contid, c.surname, c.forename, c.dob,
> c.postcode, c.filed, c.available, c.email
> FROM
> cont c
> WHERE
> EXISTS(
> SELECT 1 FROM
> contskill t1
> JOIN contskill t2 ON (t2.contid = c.contid AND t2.skillid = 17)
> JOIN contskill t3 ON (t3.contid = c.contid AND t3.skillid = 18)
> JOIN contskill t4 ON (t4.contid = c.contid AND t4.skillid = 22)
> JOIN contskill t5 ON (t5.contid = c.contid AND t5.skillid = 24)
> JOIN contskill t6 ON (t6.contid = c.contid AND t6.skillid = 29)
> JOIN contskill t7 ON (t7.contid = c.contid AND t7.skillid = 30)
> JOIN contskill t8 ON (t8.contid = c.contid AND t8.skillid = 34)
> JOIN contskill t9 ON (t9.contid = c.contid AND t9.skillid = 4209)
> JOIN contskill t10 ON (t10.contid = c.contid AND t10.skillid =

62)

> WHERE
> t1.contid = c.contid AND t1.skillid = 10)
>
> Regards,
> Arno Brinkman
> ABVisie
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> General database developer support:
> http://www.databasedevelopmentforum.com
>
> Firebird open source database (based on IB-OE) with many SQL-99

features:

> http://www.firebirdsql.org
> http://www.firebirdsql.info
>
> Support list for Interbase and Firebird users:
> firebird-support@yahoogroups.com
>
> Nederlandse firebird nieuwsgroep:
> news://newsgroups.firebirdsql.info
>