Subject Re: query help - trying to avoid another join
Author markd_mms
--- In, 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 =/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 = :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 = :MyName)

I'll try that when I get to work.