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
