Then I guess your initial query was almost correct. Try to change it like this:
select ac.AcNum, count(au.acNum) as auNum, count(int.acNum) as intNum from academic ac LEFT OUTER JOIN author au on ac.AcNum = au.AcNum LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum group by ac.AcNum having count(au.acNum) = 0 and count(int.acNum) = (select max(acNumCount) from (select count(*) as int.AcNumCount from interest int group by int.AcNum)) I assumed that table interest has at least 1 row for at least 1 academic. Pavel On Tue, Jul 21, 2009 at 9:49 AM, Hubboo<shan...@msn.com> wrote: > > Thanks for replying > > > OK we have several tables for our assignment and for this particular > question we are asked > > Q. Among the academics who have no papers, who has the greatest number of > interests.. > > I used the * just return all attributes to start with. > > When I use > > SELECT * , count( Au.AcNum ) AS num, count( i.AcNum ) AS num2 > FROM academic a > LEFT OUTER JOIN author Au ON a.AcNum = Au.AcNum > LEFT OUTER JOIN interest i ON i.AcNUm = a.AcNum > GROUP BY A.AcNum > HAVING num =0 > > This gives me the academics with 0, that part seems to be working OK, I am > struggling on how to count the second part of the question.. > > 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) > > -- > View this message in context: > http://www.nabble.com/Subqueries-tp24587437p24588040.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
