[sqlite] Sqlite subqueries

[Rob Richardson]

A Google search for "USS Yorktown" turned up the following:

"On September 21, 1997, a division by zero error on board the USS Yorktown 
(CG-48) Remote Data Base Manager brought down all the machines on the network, 
causing the ship's propulsion system to fail."

RobR

-Original Message-


To eliminate the need to reference a table would require combining  300 tables 
into one table. A user editing entries for one space could crash the whole 
system. That's basically what happened aboard the Yorktown in 1997. A cook 
trying to enter an item into the lunch menu killed the engines on the ship.

[sqlite] Sqlite subqueries

[russ lyttle]

On 02/25/2015 10:31 AM, Rob Richardson wrote:
> A Google search for "USS Yorktown" turned up the following:
> 
> "On September 21, 1997, a division by zero error on board the USS Yorktown 
> (CG-48) Remote Data Base Manager brought down all the machines on the 
> network, causing the ship's propulsion system to fail."
> 
> RobR
> 
> -Original Message-
> 
> 
> To eliminate the need to reference a table would require combining  300 
> tables into one table. A user editing entries for one space could crash the 
> whole system. That's basically what happened aboard the Yorktown in 1997. A 
> cook trying to enter an item into the lunch menu killed the engines on the 
> ship.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
That's the basic story. The DB people claimed the OS should have
protected against the division by zero, the OS people claimed the DB
should have not let the cook overflow the menu record and cause a
division by 0. The ship had to be towed in, the ship got under weigh in
a couple of hours, etc. My goal is to make sure that nothing like any of
that happens. Or if it does has the least effect possible.

It's beginning to look like I should replace the arduinos with R-Pi
model A running sqlite3. The 'a' db then becomes a data collection and
report generator, each remote has an individual 'b' db. Everything can
be managed by simple sql and Python scripts. The R-Pi has more computing
power than the old towers and are lots cheaper.





-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] Sqlite subqueries

[russ lyttle]

On 02/25/2015 09:40 AM, Igor Tandetnik wrote:
> On 2/25/2015 9:16 AM, russ lyttle wrote:
>> To eliminate the need to reference a table would require combining  300
>> tables into one table.
> 
> Yes.
> 
>> A user editing entries for one space could crash
>> the whole system.
> 
> I don't see how this follows.
> 
>> That's basically what happened aboard the Yorktown in
>> 1997. A cook trying to enter an item into the lunch menu killed the
>> engines on the ship.
> 
> Did the software store engine configuration and menu in the same table?
> Did the software need to run statements joining engine configuration
> tables with lunch menu tables, thus necessitating putting them into the
> same database? How is scattering essentially the same data across 300
> different tables is expected to help prevent a similar mishap?
> 
>> It's beginning to look like the 'b' table should be broken into a
>> separate db and the 'a' table have indicators as to which table in b.db
>> to use.
> 
> What failure mode do you envision that would be avoided by this design?

There was a lot of finger pointing about the Yorktown incident. The DB
people blaming NT, the Microsoft people blaming the DB. Apparently the
cook tried to enter more items in the supper menu than was allowed and
caused a cascading failure ending in an engine shutdown. A complete
system reboot was required. Whether or not that was due to the DB or OS
doesn't matter now. The goal is to prevent similar events. Or at least
make it obvious where the fault lies.

I was personally involved in one incident where an occupant stuck a soda
can in a damper and caused a system shutdown.

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] Sqlite subqueries

[Igor Tandetnik]

On 2/25/2015 9:16 AM, russ lyttle wrote:
> To eliminate the need to reference a table would require combining  300
> tables into one table.

Yes.

> A user editing entries for one space could crash
> the whole system.

I don't see how this follows.

> That's basically what happened aboard the Yorktown in
> 1997. A cook trying to enter an item into the lunch menu killed the
> engines on the ship.

Did the software store engine configuration and menu in the same table? 
Did the software need to run statements joining engine configuration 
tables with lunch menu tables, thus necessitating putting them into the 
same database? How is scattering essentially the same data across 300 
different tables is expected to help prevent a similar mishap?

> It's beginning to look like the 'b' table should be broken into a
> separate db and the 'a' table have indicators as to which table in b.db
> to use.

What failure mode do you envision that would be avoided by this design?
-- 
Igor Tandetnik

[sqlite] Sqlite subqueries

[russ lyttle]

On 02/24/2015 08:53 PM, Igor Tandetnik wrote:
> On 2/24/2015 8:42 PM, russ lyttle wrote:
>> The 'a' table defines spaces to be controlled, the 'b' tables the
>> control schedules and parameters.
>> It would not be unreasonable to assume the 'a' table has >100 rows.
>> Each row in the 'a' table is associated with 3 'b' tables, all the names
>> known in advance and created off line at the same time as the row in the
>> 'a' table.
>> Each 'b' table has up to 1,440 rows.
> 
> Replace these three tables with a single table, holding three times as
> many rows. It would have an extra column holding the "original source"
> indicator - a value that indicates which of the three tables this row
> originated from. Now, in table "a" store this indicator where you
> planned to store the table name.

Thanks for the input.

To eliminate the need to reference a table would require combining  300
tables into one table. A user editing entries for one space could crash
the whole system. That's basically what happened aboard the Yorktown in
1997. A cook trying to enter an item into the lunch menu killed the
engines on the ship.

It's beginning to look like the 'b' table should be broken into a
separate db and the 'a' table have indicators as to which table in b.db
to use.

If there were an sqlite3 sketch for arduino, that would work better than
my original plan. Each space could have its own copy of the tables it is
to use. Lots of database management and communications problems go away.

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] Sqlite subqueries

[R.Smith]

There's been many discussions on this topic, you can search for it, but 
I will try to recap in short:

SQL does not work like this, not in SQLite or any other SQL engine may 
an entity construct be referenced by an uncontrolled data value. Of 
course it is easy to get around this in code whereby you can read a 
table name from one DB and use it in an SQL statement for any DB, but 
the onus here is on the maker of such software to implement whatever 
safety checks are needed to prevent corruption or indeed SQL injections 
and other mischief made possible by vulnerabilities exposed in this way.

What you are trying here is not possible in pure SQL by design.

There may however be other ways of achieving your goals, maybe 
explaining to us what you would like to do in a system/setup like this 
will help - surely someone here have done some similar thing before and 
they are always glad to assist.


On 2015-02-24 11:37 PM, russ lyttle wrote:
> I got the "Using SQLite" book and didn't find the answer there, or in a
> Google, DuckDuckGo, or Gigiblast search.
> I'm trying to create a field in a table to hold the name of a second
> table, then retrieve that name for use.
> The code below is the simplest of all the things I've tried. Can anyone
> say what should be done so (10.) returns the same as (8.)?
> Thanks
>
> 1.sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY, name VARCHAR(16),
> anotherTable TEXT);
> 2.sqlite> SELECT * FROM sqlite_master;
>  table|a|a|2|CREATE TABLE a (id INTEGER PRIMARY KEY, name
> VARCHAR(16), anotherTable TEXT)
> 3.sqlite> INSERT INTO a (name, anotherTable) VALUES ('table1', 'b');
> 4.sqlite> SELECT * FROM a;
>  1|table1|b
> 5.sqlite> CREATE TABLE b (id INTEGER PRIMARY KEY, name VARCHAR(16),
> data FLOAT);
> 6.sqlite> INSERT INTO b (name, data) VALUES ('B1', 35.0);
> 7.sqlite> INSERT INTO b (name, data) VALUES ('B2', 40.0);
> 8.sqlite> SELECT * FROM b;
>  1|B1|35.0
>  2|B2|40.0
> 9.sqlite> SELECT anotherTable FROM a WHERE name='table1';
>  b
> 10.sqlite> SELECT * FROM (SELECT anotherTable FROM a Where
> name='table1');
>  b
>  sqlite>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite subqueries

[Paul Sanderson]

Search the mail list for "Column name as a variable" for a similar discussion
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 24 February 2015 at 21:58, Igor Tandetnik  wrote:
> On 2/24/2015 4:37 PM, russ lyttle wrote:
>>
>> I'm trying to create a field in a table to hold the name of a second
>> table, then retrieve that name for use.
>
>
> You can't. SQL doesn't work this way. Reconsider your design.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite subqueries

[Igor Tandetnik]

On 2/24/2015 8:42 PM, russ lyttle wrote:
> The 'a' table defines spaces to be controlled, the 'b' tables the
> control schedules and parameters.
> It would not be unreasonable to assume the 'a' table has >100 rows.
> Each row in the 'a' table is associated with 3 'b' tables, all the names
> known in advance and created off line at the same time as the row in the
> 'a' table.
> Each 'b' table has up to 1,440 rows.

Replace these three tables with a single table, holding three times as 
many rows. It would have an extra column holding the "original source" 
indicator - a value that indicates which of the three tables this row 
originated from. Now, in table "a" store this indicator where you 
planned to store the table name.
-- 
Igor Tandetnik

[sqlite] Sqlite subqueries

[russ lyttle]

Thanks. The application is for an energy conservation application.

The 'a' table defines spaces to be controlled, the 'b' tables the
control schedules and parameters.
It would not be unreasonable to assume the 'a' table has >100 rows.
Each row in the 'a' table is associated with 3 'b' tables, all the names
known in advance and created off line at the same time as the row in the
'a' table.
Each 'b' table has up to 1,440 rows.

The application will loop through the 'a' table, recover a row from the
target 'b' table and execute it on the space described in the 'a' table.
Users can edit (add or delete rows) in the 'b' tables, but nothing else.

There are also 'c' and 'd' tables.
I could do this by composing queries outside sqlite. But that doesn't
seem very elegant and more error prone.

Because the 'a' table looks a lot like the sqlite_master table, I
thought there might be a way do do it all in an sql script.


On 02/24/2015 06:12 PM, R.Smith wrote:
> There's been many discussions on this topic, you can search for it, but
> I will try to recap in short:
> 
> SQL does not work like this, not in SQLite or any other SQL engine may
> an entity construct be referenced by an uncontrolled data value. Of
> course it is easy to get around this in code whereby you can read a
> table name from one DB and use it in an SQL statement for any DB, but
> the onus here is on the maker of such software to implement whatever
> safety checks are needed to prevent corruption or indeed SQL injections
> and other mischief made possible by vulnerabilities exposed in this way.
> 
> What you are trying here is not possible in pure SQL by design.
> 
> There may however be other ways of achieving your goals, maybe
> explaining to us what you would like to do in a system/setup like this
> will help - surely someone here have done some similar thing before and
> they are always glad to assist.
> 
> 
> On 2015-02-24 11:37 PM, russ lyttle wrote:
>> I got the "Using SQLite" book and didn't find the answer there, or in a
>> Google, DuckDuckGo, or Gigiblast search.
>> I'm trying to create a field in a table to hold the name of a second
>> table, then retrieve that name for use.
>> The code below is the simplest of all the things I've tried. Can anyone
>> say what should be done so (10.) returns the same as (8.)?
>> Thanks
>>
>> 1.sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY, name VARCHAR(16),
>> anotherTable TEXT);
>> 2.sqlite> SELECT * FROM sqlite_master;
>>  table|a|a|2|CREATE TABLE a (id INTEGER PRIMARY KEY, name
>> VARCHAR(16), anotherTable TEXT)
>> 3.sqlite> INSERT INTO a (name, anotherTable) VALUES ('table1', 'b');
>> 4.sqlite> SELECT * FROM a;
>>  1|table1|b
>> 5.sqlite> CREATE TABLE b (id INTEGER PRIMARY KEY, name VARCHAR(16),
>> data FLOAT);
>> 6.sqlite> INSERT INTO b (name, data) VALUES ('B1', 35.0);
>> 7.sqlite> INSERT INTO b (name, data) VALUES ('B2', 40.0);
>> 8.sqlite> SELECT * FROM b;
>>  1|B1|35.0
>>  2|B2|40.0
>> 9.sqlite> SELECT anotherTable FROM a WHERE name='table1';
>>  b
>> 10.sqlite> SELECT * FROM (SELECT anotherTable FROM a Where
>> name='table1');
>>  b
>>  sqlite>
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 

[sqlite] Sqlite subqueries

[Igor Tandetnik]

On 2/24/2015 4:37 PM, russ lyttle wrote:
> I'm trying to create a field in a table to hold the name of a second
> table, then retrieve that name for use.

You can't. SQL doesn't work this way. Reconsider your design.
-- 
Igor Tandetnik

[sqlite] Sqlite subqueries

[russ lyttle]

I got the "Using SQLite" book and didn't find the answer there, or in a
Google, DuckDuckGo, or Gigiblast search.
I'm trying to create a field in a table to hold the name of a second
table, then retrieve that name for use.
The code below is the simplest of all the things I've tried. Can anyone
say what should be done so (10.) returns the same as (8.)?
Thanks

1.sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY, name VARCHAR(16),
anotherTable TEXT);
2.sqlite> SELECT * FROM sqlite_master;
table|a|a|2|CREATE TABLE a (id INTEGER PRIMARY KEY, name
VARCHAR(16), anotherTable TEXT)
3.sqlite> INSERT INTO a (name, anotherTable) VALUES ('table1', 'b');
4.sqlite> SELECT * FROM a;
1|table1|b
5.sqlite> CREATE TABLE b (id INTEGER PRIMARY KEY, name VARCHAR(16),
data FLOAT);
6.sqlite> INSERT INTO b (name, data) VALUES ('B1', 35.0);
7.sqlite> INSERT INTO b (name, data) VALUES ('B2', 40.0);
8.sqlite> SELECT * FROM b;
1|B1|35.0
2|B2|40.0
9.sqlite> SELECT anotherTable FROM a WHERE name='table1';
b
10.sqlite> SELECT * FROM (SELECT anotherTable FROM a Where
name='table1');
b
sqlite>


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 

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" 
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

[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

[sqlite] Subqueries

[Hubboo]

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