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

> Don't quite understand your question, but

Yeah I didn't after reading it again either. What I'm trying to do is
search for product by a particular artist. The artist name may be a
primary name but it may also be a secondary name for another artist.
At the moment all I do is...

select fields
from product
join product_participant on...
join participant on...
where participant.name =/starts withs/containing/ 'name'

but I need to be able to search for secondary names that also match
'name', and those secondary names don't have to be relate back to a
participant with the same primary name. So say I search for 'Sting'
and there is a participant with a primary name of Sting that I get all
the products for, but there is also a participant with a primary name
of 'Bee' and a secondary name of 'Sting', I need to be able to return
the results for that participant as well, hopefully without adding
another join.

>
> select <fields>
> from product_participant pp
> join participant_name pn
> on pp.participantid = pn.participantid
> where pn.name = :MyName
>

That's what I want to avoid.

> sounds like a partial solution. With an EXISTS, this could be translated
>
> select <fields>
> from product_participant pp
> where exists(select * from participant_name pn
> where pp.participantid = pn.participantid
> and pn.name = :MyName)
>

I'll try that when I get to work.

Thanks,
Mark