Re: [sqlite] Subqueries

[Hubboo]

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

[Hubboo]

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

[Igor Tandetnik]

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

[Jim Showalter]

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

Re: [sqlite] Subqueries

[Hubboo]

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

[Hubboo]

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

[Igor Tandetnik]

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

[Pavel Ivanov]

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

[Hubboo]

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

[Pavel Ivanov]

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

[Hubboo]

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

[Pavel Ivanov]

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