Thanks Set, the DISTINCT is needed because otherwise the result set
will contain duplicate Person rows.

Yeah, I had a query similar to yours but it seemed quite complex. It
seems like the only way to simplify the query is to denormalize the
refTypeID field by adding it to the Links table.

R.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> Robert DiFalco wrote:
> > Can anyone think of a clean way to do this or do I have to get rid of
> > the joins altogether and do a subquery for each StringValue search

term?

> >
> > What is below works but does not seem like a useable option:
> >
> > SELECT DISTINCT P.*
> > FROM Person P
> >
> > LEFT JOIN StringLinks L1 ON P.ID = L1.parentID
> > LEFT JOIN StringValue SV1 ON L1.ID = SV1.ID AND SV1.refTypeID = 1
> > LEFT JOIN StringType ST1 ON ST1.ID = 1
> >
> > LEFT JOIN StringLinks L2 ON P.ID = L2.parentID
> > LEFT JOIN StringValue SV2 ON L2.ID = SV2.ID AND SV2.refTypeID = 2
> > LEFT JOIN StringType ST2 ON ST2.ID = 2
> >
> > WHERE ( SV1.f_val LIKE 'foo' OR
> > ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
> > AND
> > ( SV2.f_val LIKE 'bar' OR
> > ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
>
> Hi Robert!
>
> I can think of another way to do this, don't know if it is cleaner

or not:

>
> SELECT P.*
> FROM Person P
> where (exists(select *
> from StringLinks L1
> JOIN StringValue SV1 ON L1.ID = SV1.ID
> AND SV1.refTypeID = 1
> where P.ID = L1.parentID
> and SV1.f_val LIKE 'foo')
> or (not exists(select *
> from StringLinks L2
> JOIN StringValue SV2 ON L2.ID = SV2.ID
> AND SV2.refTypeID = 1
> where P.ID = L2.parentID
> and SV2.f_val LIKE 'foo')
> and exists(select *
> from StringType ST1
> where ST1.ID = 1
> and ST1.defaultVal LIKE 'foo')))
> and (exists(select *
> from StringLinks L3
> JOIN StringValue SV3 ON L3.ID = SV3.ID
> AND SV3.refTypeID = 2
> where P.ID = L3.parentID
> and SV3.f_val LIKE 'bar')
> or (not exists(select *
> from StringLinks L4
> JOIN StringValue SV4 ON L4.ID = SV4.ID
> AND SV4.refTypeID = 2
> where P.ID = L4.parentID
> and SV4.f_val LIKE 'bar')
> and exists(select *
> from StringType ST2
> where ST2.ID = 2
> and ST2.defaultVal LIKE 'bar')))
>
> Basically, I've just tried to translate your query into using EXISTS
> rather than LEFT JOIN. I might have forgotten something, you'll see

when

> you test the query if there is missing parenthesis or if I've forgotten
> to change an alias somewhere (this reply has extensively used copy and
> paste).
>
> My query will return the first record quicker than yours (since it
> doesn't have to do any sorting), but I'm more uncertain which of the

two

> will return the entire result set the quickest. If you use IBO, one
> benefit of my suggested query, is that IBO will know how to make it
> updatable.
>
> Also, have you tried your original query above without the DISTINCT?
>
> HTH,
> Set
>