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,2Because 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)
>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
>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,
>>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 >>>>