Although your query doesn't make sense without any explanation of what did you mean and how it is supposed too work I can provide you a couple of observations:
1) Do you realize that select * doesn't make any sense in this query? The only meaningful field will be ac.AcNum, all others will be essentially trashed? 2) Looking at your query I can assume that none of your tables contain column auNum. But nonetheless you're having "having auNum = 0" at the most inner query. I guess it's not what you supposed to write there. If these are not your problem then you better explain what do you want to obtain from this query and what does it return to you. Pavel On Tue, Jul 21, 2009 at 9:14 AM, Hubboo<shan...@msn.com> wrote: > > Hi, > > I am doing an assignment using SQLite and was wondering if someone could > tell me why this doesn't work and maybe offer some help please? > > select *, count(distinct 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(distinct au.acNum) = > > (select Max(int.acNumCount) > from (select count(int.acNum) as int.AcNumCount > 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 auNum = 0)) > -- > View this message in context: > http://www.nabble.com/Subqueries-tp24587437p24587437.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
