Hello
I'm currently evaluating SQLite for replacing a Microsoft Access (Jet) Database
in one of our projects. We use a data layer abstraction which allows us to
quickly implement other database. Therefore we allow the user of the data acces
layer to make arbitrary nested joins. Currently I have a problem when using a
nested join as a right hand side of another join (doesn't depent if it's an
inner join or left outer join).
I use the following test database.
CREATE TABLE TableA (
ID integer PRIMARY KEY NOT NULL,
ForeignB integer NOT NULL DEFAULT -1
);
CREATE TABLE TableB (
ID integer PRIMARY KEY NOT NULL,
ForeignC integer NOT NULL DEFAULT -1
);
CREATE TABLE TableC (
ID integer PRIMARY KEY NOT NULL,
Value integer NOT NULL DEFAULT -1
);
INSERT INTO TableA(ForeignB) Values(1);
INSERT INTO TableA(ForeignB) Values(-1); INSERT INTO TableB(ForeignC)
Values(1); INSERT INTO TableC(Value) Values(333);
Basically TableA has an optional foreign key to TableB and TableB has a foreign
key to TableC.
Now we would like to execute the following SQL.
SELECT *
FROM
TableA
LEFT OUTER JOIN
(
TableB
INNER JOIN TableC
ON TableB.ForeignC=TableC.ID
)
ON TableA.ForeignB=TableB.ID;
Executing the statement above yields to a 'SQL Error: no such column:
TableB.ID'. Although accordingly to http://sqlite.org/lang_select.html this
should be a valid statement. Executing the same statement on other databases
e.g. MsAccess/SQL Server works as expected.
Same applies if one replaces the LEFT OUTER JOIN with an INNER JOIN.
SELECT *
FROM
TableA
INNER JOIN
(
TableB
INNER JOIN TableC
ON TableB.ForeignC=TableC.ID
)
ON TableA.ForeignB=TableB.ID;
Executing the statement above yields to a 'SQL Error: no such column:
TableB.ID'. I know one could say, the Join (TableA -> (Nested)) doesn't know
anything about TableB. But let's try to change the order of the join.
SELECT *
FROM
(
TableB
INNER JOIN TableC
ON TableB.ForeignC=TableC.ID
)
INNER JOIN
TableA
ON TableA.ForeignB=TableB.ID;
Executing this statement now works as expected, without any error. But here one
could also say the join ( (Nested) -> TableA)) doesn't know anything about
TableB but still it works. Also the documentation of SQLite states the
optimizer could rearrange the joins, with this inner join example then it
shouldn't make any difference if the nested join is used as left or right hand
side of the join.
I know one could rewrite both inner join examples without any brackets, leading
to the same results of the queries. But for the first example, the left outer
join one, it's not possible.
Another really interesting query is the following.
SELECT *
FROM
TableA
INNER JOIN
(
TableB
INNER JOIN TableC
ON TableB.ForeignC=TableC.ID
) As T
ON TableA.ForeignB=T.ID;
This one works, but which column will be used as T.ID, because both TableB and
TableC have a column ID. Also no ambiguous column error is raised.
Thanks for any help regarding this problem.
Kind Regards
Fabrizio
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
