From: John LeSueur <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] New Query Question
Date: Tue, 26 Jul 2005 19:51:54 -0600
MIME-Version: 1.0
Received: from sqlite.org ([67.18.92.124]) by mc11-f29.hotmail.com with
Microsoft SMTPSVC(6.0.3790.211); Tue, 26 Jul 2005 18:51:47 -0700
Received: (qmail 22997 invoked by uid 1000); 27 Jul 2005 01:46:34 -0000
Received: (qmail 22990 invoked from network); 27 Jul 2005 01:46:34 -0000
X-Message-Info: JGTYoYF78jEHjJx36Oi8+Z3TmmkSEdPtfpLB7P/ybN8=
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
Precedence: bulk
List-Id: SQLite Users Mailing List <sqlite-users.sqlite.org>
List-Post: <mailto:sqlite-users@sqlite.org>
List-Help: <mailto:[EMAIL PROTECTED]>
List-Unsubscribe: <mailto:[EMAIL PROTECTED]>
List-Subscribe: <mailto:[EMAIL PROTECTED]>
Delivered-To: mailing list sqlite-users@sqlite.org
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
References: <[EMAIL PROTECTED]>
X-Virus-Scanned: amavisd-new at supernerd.com
Return-Path:
[EMAIL PROTECTED]
X-OriginalArrivalTime: 27 Jul 2005 01:51:47.0466 (UTC)
FILETIME=[BF9AD6A0:01C5924D]
David Fowler wrote:
Really sorry guys, AS files, should indeed be AS tables, thats what does
not work. Sorry again for the copy/pasting/editing error. When I remember
to name everything correctly, I still can't select anything by
table.column sytax that is in the derived table, wether the derived table
is aliased or not (I think it has to be aliased in SQLite, but not in
mySQL).
So to recap, this is what doesn't work, but I think should.
SELECT tables.id FROM (
SELECT table4.location, table4.id
for this line, try
SELECT table4.location as location, table4.id as id
FROM table1
INNER JOIN table2 ON (table1.class_id = table2.class_id)
INNER JOIN table3 ON (table2.name_id = table3.name_id)
INNER JOIN table4 ON (table4.id = table3.id)
INNER JOIN table5 ON (table4.table5_id = table5.id)
) AS tables GROUP BY tables.id
;
This doesnt work either:
SELECT table4.id FROM (
SELECT table4.location, table4.id
FROM table1
INNER JOIN table2 ON (table1.class_id = table2.class_id)
INNER JOIN table3 ON (table2.name_id = table3.name_id)
INNER JOIN table4 ON (table4.id = table3.id)
INNER JOIN table5 ON (table4.table5_id = table5.id)
) GROUP BY table4.id
;
Nor does specifying id only.
Any input would be appreciated, as I cant think of a way I can work around
this yet. Thanks
========================================================
SQLites behaviour is just getting worse.
SELECT tables.* FROM (
SELECT table.field1, table.field2
FROM table
) as tables
;
This will work.
But as soon as i do the following it errors with invalid column again
SELECT tables.field1 FROM (
SELECT table.field1, table.field2
try:
SELECT table.field1 as field1, table.field2 as field2
FROM table
) as tables
;
And when I do this, it works again
SELECT tables.field1 FROM (
SELECT field1, field2
FROM table
) as tables
;
My problem is when i do the latter with a larger query (SELECTs from more
than one table), the column name becomes ambiguous, and the query fails
once again.
My problems with SQLite are really starting to get to me, I don't want to
go back to MySQL, even if it is whats currently working, I moved to this
database for its footprint and speed, but its features are starting to get
me worried. What else does this database do wrong?
This is an issue with the column names being returned by the engine. I
think that theres a pragma that makes it behave as you expect it to. It's
some combination of having
pragma short_column_names = 0;
and some other pragma like full_column_names or something there is a wiki
page, as well as a bunch of discussion about this topic on the mailing
list.
One thing that I've made a habit of is aliasing all my column names, all
the time, no matter what.
John LeSueur
Thanks John, it's now working well. Do you have a link to the wiki page with
this information on as I cannot find it myself. Can't believe I didn't think
to alias the columns, think I will start aliasing everything all the time
now, just incase. Thanks again for the solution!
- Dave
