[sqlite] Sqlite subqueries

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-

[sqlite] Sqlite subqueries

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

[sqlite] Sqlite subqueries

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

[sqlite] Sqlite subqueries

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

[sqlite] Sqlite subqueries

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

[sqlite] Sqlite subqueries

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

[sqlite] Sqlite subqueries

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

[sqlite] Sqlite subqueries

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

[sqlite] Sqlite subqueries

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,

[sqlite] Sqlite subqueries

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

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

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

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

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

Re: [sqlite] Subqueries

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] S

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 ]

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

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,

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

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

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

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(

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

[sqlite] Subqueries

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