Sergey,

SQL 1 returns records that should not be in the file. The total returned should be 14 as SQL 2 statement would then duplicate each for Asia and Africa. I suspect the reason is you will find more than one record with the same genus and species if the same continent, but a different site. (Please bear with me, this is my daughter's data.)

Ken


>Ken,
>
>Try

* #1
>SELECT genus, species ;
>	FROM species
>		JOIN site ON species.pkey = site.fkey ;
>	WHERE site.continent IN ('Asia', 'Africa') ;
>	GROUP BY 1,2 ;
>	HAVING COUNT(*) = 2 ;
>	INTO CURSOR curBoth
>
>* #2
>SELECT genus, species,  'Asia  ' AS continent  ;
>	FROM curBoth ;
>	UNION ALL ;
>SELECT genus, species,  'Africa' AS continent  ;
>	FROM curBoth ;
>	INTO CURSOR curAA
>
>* Or another #2
>SELECT genus, species, continent ;
>	FROM species
>		JOIN site ON species.pkey = site.fkey ;
>	WHERE site.continent IN ('Asia', 'Africa') ;
>		AN genus + species IN ( ;
>			SELECT genus + species FROM curBoth)
>	INTO CURSOR curAA
>

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