Hi Agnes and Fabio and Ken,

Though your solution should work, I'd rather would choose for:

SELECT genus, species, continent ;
    FROM Species P INNER JOIN Site S ON P.Pkey = S.fkey AND S.continent IN ('Africa','Asia') ;
    WHERE EXISTS (SELECT * FROM Site WHERE fKey = P.Key AND Continent = 'Asia') AND ;
          EXISTS (SELECT * FROM Site WHERE fKey = P.Key AND Continent = 'Africa') ;
    ORDER BY 1,2

Because to me, appart from my personal opinion it is more readable, this is also easier to extent when you want to query upon species that occur in three continents (just add another EXIST clause). Also with this construct you can solve queries like give the species that do occur in asia and africa, but not in europe (using am extra NOT EXIST clause)

Don't let the EXISTS SUBquery fool you. VFP treats this as ONE query and is optimized as such. you can use up to 9 EXIST clauses in one query because it internally is very much implemented like a JOIN. Personally I find the SQL EXISTS clause one of the most underestimated construct in SQL queries.

Walter,






>
>PMFJI
>
>from my knowledge it is better to do
>

>SELECT genus, species, continent ;
>    FROM  Site S;
>    INNER JOIN Species ON S.fkey =Species.Pkey AND S.continent IN ('Africa','Asia');
>    INNER JOIN Site X ON X.fkey =Species.Pkey AND X.continent IN ('Africa','Asia') AND X.continent#S.continent ;
>ORDER BY 1,2
>

>Agnes
>Update:
>I mean
>
>

>SELECT genus, species, continent ;
>    FROM  Site S;
>    INNER JOIN Species ON S.fkey =Species.Pkey AND S.continent IN ('Africa','Asia');
>    INNER JOIN Site X ON X.fkey =Species.Pkey AND X.continent IN ('Africa','Asia')
>where X.continent#S.continent ;
>ORDER BY 1,2
>

>>Hi Ken,
>>
>>try this:
>>

>>SELECT genus, species, continent ;
>>    FROM Species ;
>>    INNER JOIN Site S ON S.fkey =Species.Pkey AND S.continent IN ('Africa','Asia');
>>    INNER JOIN Site X ON X.fkey =Species.Pkey AND X.continent IN ('Africa','Asia') AND X.continent#S.continent ;
>>ORDER BY 1,2
>>

>>
>>Fabio