General information
Category:
Coding, syntax & commands
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)
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only