I recommend starting with a smaller query and adding to it. For example, can you do a select count from the table? Then can you do a select * from the table? Then can you do a select * with an order by? And so forth, building up the query one piece at a time until it does what you want.
I'm not smart enough to write complex (or even pretty simple) queries in one go. Iterative development works well for me. ----- Original Message ----- From: "Hubboo" <shan...@msn.com> To: <sqlite-users@sqlite.org> Sent: Tuesday, July 21, 2009 7:55 AM Subject: Re: [sqlite] Subqueries > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
