Hi Ken,

>I tested this and it returns no records. I assumed it was due the EXIST select statement which refers to the p.key but the p is not identified as data source in the select. When I added species as data source, it returns a huge file (2 million records). I'm expecting 28 records.


Of course, I don't have your dataset, but a simple test at my site says it should work (Though P.key should be P.Pkey). The P.Pkey refers to the first select and should be really O.K. Maybe you should first remove the EXIST clause to ensure that the IN ('Africa','Asia') is working correctly (Spelling mistake or SQL EXACT settings or so).

IF it is working correctly, Add one EXIST clause and see what happens. If all is well add the other one and check it again.

Also make sure you've got an index on pkey, fkey and continent to ensure maximum performance.

Walter,


>Ken
>
>
>Hi Ken,
>>
>>How about,
>>
>>

>>SELECT genus, species, continent ;
>>    FROM Species P INNER JOIN Site S ON P.Pkey = S.fkey ;
>>    WHERE S.continent IN ('Africa','Asia') AND ;
>>        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

>>
>>Walter,