markd_mms wrote:

> I have a table at the moment that lists names of music artists. An
> artist can have more than one name although only the primary name is
> shown when displaying search results - other names can be search for
> but never displayed. Artist names are related through their
> participantid which is listed in the participant table, along with the
> primary name. Both tables look like this:
>
> CREATE TABLE PARTICIPANT (
> PARTICIPANTID INTEGER NOT NULL,
> NAME VARCHAR(128) NOT NULL,
> UPPER_NAME VARCHAR(128) NOT NULL,
> SORT_NAME VARCHAR(128) NOT NULL);
>
> CREATE TABLE PARTICIPANT_NAME (
> NAMEID INTEGER NOT NULL,
> PARTICIPANTID INTEGER NOT NULL,
> NAME VARCHAR(128) NOT NULL,
> SORT_NAME VARCHAR(128) NOT NULL,
> UPPER_NAME VARCHAR(128) NOT NULL);
>
> Artists are related to their product through the product_participant
> table:
>
> CREATE TABLE PRODUCT_PARTICIPANT (
> PRODUCTID INTEGER NOT NULL,
> PARTICIPANTID INTEGER NOT NULL,
> PARTICIPANT_TYPEID SMALLINT);
>
> Searching for an artists primary name is easy enough - just join the
> participant table and search for matching names. I know I can do the
> same with participant_name to search for an artists other name but I'd
> like to do it without using a join - I'm not really sure why I feel
> the need to avoid another join other than because I never actually
> display anything from the participant_name table.
>
> I've tried playing around with IN and EXISTS but they return the wrong
> results and I just can't to get my head around how it should work.
> Does anyone have any ideas?
>
> TIA

Don't quite understand your question, but

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

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)

but that would require product_participant to go NATURAL and would be
somewhat slow if we're talking about millions of records in this table
(it doesn't matter how many records there are in participant_name).

JOINs are generally good, at least as we're talking about normal inner
joins. LEFT (RIGHT/FULL OUTER) JOINs should be used with a bit more
care, though used correctly they're very good as well.

HTH,
Set