Fabio,


>

>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
>

>If i want a general solution, then:
>

>SELECT genus, species, continent ;
>	FROM Species P JOIN Site S ON P.Pkey = S.fkey AND S.continent IN (ContinentList) ;
>	WHERE P.Pkey IN (SELECT fkey FROM Site WHERE Continent IN (ContinentList) GROUP BY 1 HAVING COUNT(*)=ContinentListCount) ;
>	ORDER BY 1,2
>

You need a GROUP BY 1 HAVING COUNT(distinct continent) = ContinentListCount because else if in the site table the combination of a certain species and continent occurs more than once, your
query fails.

The same counts for the OUTER SELECT which needs a DISTINCT.

Since you know the continents you want to list already it might be better to do something like this:

SELECT genus, species ;
    FROM Species P  ;
    WHERE ;
        EXISTS (SELECT * FROM Site WHERE fKey = P.PKey AND Continent = 'Asia') AND ;
        EXISTS (SELECT * FROM Site WHERE fKey = P.PKey AND Continent = 'Africa') ;
    ORDER BY 1,2

Which seem to be able to return the resultset within a second in a table with more than 1 million records (with the proper indexes).

Walter,