Re: [sqlite] Why this SQL does not work?

2004-11-11 Thread Derrell . Lipman 
amead <[EMAIL PROTECTED]> writes:

> Brass Tilde wrote:
>
>select ID,
>(select  SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID

> Hmm.. thanks but I'm still confused... isn't the above an example of a 
> non-static query that used variables from the main query?

If it uses variables from the main query, then by SQLite's definition, it's
not a static query.  Another way to put it, is that subqueries in SQLite may
not use variables from the main query.

Derrell

Re: [sqlite] Why this SQL does not work?

2004-11-11 Thread amead 
Brass Tilde wrote:
select ID,
(select  SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID
   

from B [...]
 

 

It mentions, among others:
  Variable subqueries
  Subqueries must be static. They are evaluated only once. They
  may not, therefore, refer to variables in the main query.
 

 

I'm not the original poster.. but I'm trying to come up to speed on
SQL/sqlite and I'm having some trouble understanding this... What is an
example of a static subquery?
   

The above is an example of a static sub-query.  It's a query within a query.
 

Hmm.. thanks but I'm still confused... isn't the above an example of a 
non-static query that used variables from the main query?

-Alan

Re: [sqlite] Why this SQL does not work?

2004-11-11 Thread Brass Tilde 
> >>select ID,
> >>(select  SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID
> >>from B [...]
> >>

> >It mentions, among others:
> >Variable subqueries
> >Subqueries must be static. They are evaluated only once. They
> >may not, therefore, refer to variables in the main query.

> I'm not the original poster.. but I'm trying to come up to speed on
> SQL/sqlite and I'm having some trouble understanding this... What is an
> example of a static subquery?

The above is an example of a static sub-query.  It's a query within a query.

> And how would one re-write this to
> eliminate the subquery?

select B.ID, A.SERVER_ID from B inner join A on B.GROUP_ID = A.ID

or

select  B.ED, A.SERVER_ID
fromA, B
where   A.ID = B.GROUP_ID

I think.

Re: [sqlite] Why this SQL does not work?

2004-11-11 Thread amead 
Gerhard Haering wrote:
On Thu, Nov 11, 2004 at 09:40:44AM -0500, [EMAIL PROTECTED] wrote:
 

[...] The following SQL does not  work, complaint is "Error: no such column: 
B.GROUP_ID"

select ID, 
(select  SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID
from B [...]
   

Looks like you haven't read this resource, yet:
   http://sqlite.org/omitted.html
It mentions, among others:
   Variable subqueries
   Subqueries must be static. They are evaluated only once. They
   may not, therefore, refer to variables in the main query.
 

I'm not the original poster.. but I'm trying to come up to speed on 
SQL/sqlite and I'm having some trouble understanding this... What is an 
example of a static subquery?  And how would one re-write this to 
eliminate the subquery?

-Alan

Re: [sqlite] Why this SQL does not work?

2004-11-11 Thread Gerhard Haering 
On Thu, Nov 11, 2004 at 09:40:44AM -0500, [EMAIL PROTECTED] wrote:
> [...] The following SQL does not  work, complaint is "Error: no such column: 
> B.GROUP_ID"
> 
> 
> select ID, 
> (select  SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID
> from B [...]

Looks like you haven't read this resource, yet:

http://sqlite.org/omitted.html

It mentions, among others:
Variable subqueries
Subqueries must be static. They are evaluated only once. They
may not, therefore, refer to variables in the main query.

-- Gerhard
-- 
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?


signature.asc
Description: Digital signature

[sqlite] Why this SQL does not work?

2004-11-11 Thread WeiChin3 
Hi,
 
I have two tables:
 
CREATE TABLE A (
ID  integer primary key,
SERVER_ID integer
)

CREATE TABLE B (
ID integer primary key,
GROUP_ID integer,
SERVER_ID integer 
)
 
The following SQL does not  work, complaint is "Error: no such column: 
B.GROUP_ID"


select ID, 
(select  SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID
from B
 
I realize that I can do it  in other ways to make it work. However, anyone 
has an explanation why this SQL  does not work with SQLITE? It works with MYSQL 
and MS ACCESS.
 
Thanks in  advance.
 
Wei