I'm struggling to write SQL code that identifies the intersection of some values. The tables in question are a site table (containing location of fauna) and species table (containing genus, species, etc). Here's my problem: I want to select all genus and species from the species table that are found in both Asia and Africa (in the site table). The output table should contain 2 records for each common genus and species, one for Asia and one for Africa.

I'm "solved" the problem by writing three SQL statements. The first creates a cursor of all genus and species in Asia. The second SQL statement creates a second cursor of all genus and species in Africa. My third SQL statement selects the common genus and species. I've listed the code below....my question is there a more direct way to identify this data? It works, but I would think there is more elegant solution.


First SQL statement:

Select genus, species, continent, genus+species as gsp ;
from species, site ;
where site.continent = 'Asia' ;
species.pkey = site.fkey ;
into cursor curAsia

Second SQL statement:

Select genus, species, continent, genus+species as gsp ;
from species, site ;
where site.continent = 'Africa' ;
species.pkey = site.fkey ;
into cursor curAfrica

Third SQL Statement:

Select curAsia.genus, curAsia.species, curAsia.continent ;
from curAsia ;
where curAsia.gsp in (select curAfrica.gsp from curAfrica) ;
into cursor curAA ;
order by 1, 2 ;
union ;
Select curAfrica.genus, curAfrica.species, curAfrica.continent ;
from curAfrica ;
where curAfrica.gsp in (select curAsia.gsp from curAsia)