Thanks for your reply igor but i get this error Likely SQL syntax error: select * from Academic where AcNum = ( select AcNum from Interest where AcNum not in (select AcNum from Author) group by AcNum order by count(*) desc limit 1 ); [ near "AcNum": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]
Igor Tandetnik wrote: > > Hubboo <shan...@msn.com> wrote: >> Q. Among the academics who have no papers, who has the greatest >> number of interests.. >> >> Database looks like >> >> Department(DeptNum, Descrip, Instname, DeptName, State, Postcode) >> Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title) >> Paper(PaNum, Title) >> Author(PaNum, AcNum) >> Field(FieldNum, ID, Title) >> Interest(FieldNum, AcNum, Descrip) > > Try this: > > select * from Academic > where AcNum = ( > select AcNum from Interest > where AcNum not in (select AcNum from Author) > group by AcNum > order by count(*) desc limit 1 > ); > > Igor Tandetnik > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24589275.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users