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