Re: [sqlite] Subqueries
Ah it worked actually when i typed it and not pasted. Hubboo wrote: > > AH maybe its something to do with portable firefox > > Igor Tandetnik wrote: >> >> Hubboo wrote: >>> 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] >> >> I've actually tested this statement using the database schema you >> provided, and it worked for me. The statement is syntactically correct. >> So, either you described your database schema incorrectly, or there's a >> problem with whatever wrapper you are using to access SQLite. >> >> 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-tp24587437p24590028.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
Re: [sqlite] Subqueries
AH maybe its something to do with portable firefox Igor Tandetnik wrote: > > Hubboo wrote: >> 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] > > I've actually tested this statement using the database schema you > provided, and it worked for me. The statement is syntactically correct. > So, either you described your database schema incorrectly, or there's a > problem with whatever wrapper you are using to access SQLite. > > 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-tp24587437p24589915.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
Re: [sqlite] Subqueries
Hubboo wrote: > 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] I've actually tested this statement using the database schema you provided, and it worked for me. The statement is syntactically correct. So, either you described your database schema incorrectly, or there's a problem with whatever wrapper you are using to access SQLite. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subqueries
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" To: 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 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
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 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
Re: [sqlite] Subqueries
Nah still get this error Likely SQL syntax error: 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 acNumCount from interest int group by int.AcNum)) [ near " ": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] Pavel Ivanov-2 wrote: > > Sorry, mis-looked: "as int.AcNumCount" should be "as AcNumCount". > Don't see other syntax errors. > > Pavel > > On Tue, Jul 21, 2009 at 10:21 AM, Hubboo wrote: >> >> Thanks. Returns an error >> >> Likely SQL syntax error: 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)) >> >> [ near " ": syntax error ] >> Exception Name: NS_ERROR_FAILURE >> Exception Message: Component returned failure code: 0x80004005 >> (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] >> >> -- >> View this message in context: >> http://www.nabble.com/Subqueries-tp24587437p24588626.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 > > -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24589179.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
Re: [sqlite] Subqueries
Hubboo 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
Re: [sqlite] Subqueries
Sorry, mis-looked: "as int.AcNumCount" should be "as AcNumCount". Don't see other syntax errors. Pavel On Tue, Jul 21, 2009 at 10:21 AM, Hubboo wrote: > > Thanks. Returns an error > > Likely SQL syntax error: 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)) > > [ near " ": syntax error ] > Exception Name: NS_ERROR_FAILURE > Exception Message: Component returned failure code: 0x80004005 > (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] > > -- > View this message in context: > http://www.nabble.com/Subqueries-tp24587437p24588626.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
Re: [sqlite] Subqueries
Thanks. Returns an error Likely SQL syntax error: 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)) [ near " ": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] -- View this message in context: http://www.nabble.com/Subqueries-tp24587437p24588626.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
Re: [sqlite] Subqueries
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 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
Re: [sqlite] Subqueries
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
Re: [sqlite] Subqueries
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 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