Not really, then I would have to select child table with which to JOIN on condition, based on the value of parent table.
CREATE TABLE parent( id INTEGER PRIMARY KEY, child_type INTEGER, CHECK(child_type IN (1, 2)) ); CREATE TABLE child_1( id INTEGER PRIMARY KEY, my_value INTEGER, ... ); CREATE TABLE child_2( id INTEGER PRIMARY KEY, my_value INTEGER, my_other value INTEGER, ... ); CREATE TABLE parent_child_1_link( parent_id INTEGER PRIMARY KEY, child_1_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(id), FOREIGN KEY(child_1_id) REFERENCES child_1(id) ); CREATE TABLE parent_child_2_link( parent_id INTEGER PRIMARY KEY, child_2_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(id), FOREIGN KEY(child_2_id) REFERENCES child_2(id) ); now, depending on the child_type in the parent I want to select * child_type * child_id * my_value of specific child * some other values of specific child * some other values of parent I cannot JOIN obviously, so I decided to first fetch specific ID of a child, alias it and then use it in selection of properties of specific child. This way i would avoid querying parent_child_1_link or parent_child_2_link tables for each property of specific child. But i think 2 queries will work more efficiently. (SIDE NOTE: I know about necessity of indices for FKs, I decided to omit them because they are meaningless in this example) 8 February 2016, 13:42:04, by "J Decker" <d3ck0r at gmail.com>: > On Mon, Feb 8, 2016 at 3:38 AM, Paul wrote: > > I see, thank you for pointing out. > > > > I wanted to use it on table with conditional relations with 3 different > > child tables. > > Though I could use a trick and fit data selection into one query, > > efficiently. > > Alas I am forced to stick to 2 queries. > > > > Might still be able to collapse it into one CTE query.... but I don't > know what the other part is to demo that... > > https://www.sqlite.org/lang_with.html > > > Thank you! > > > > 8 February 2016, 12:08:26, by "Clemens Ladisch" : > > > >> Paul wrote: > >> > I am curious what is a particular reason that aliased columns in a query > >> > not visible to sub-queries? > >> > >> Because the SQL standard says so. > >> > >> > Of course it is possible to duplicate expression in sub-query ... > >> > But this would not be as efficient as to access result of already > >> > evaluated expression. > >> > >> An alias refers not to an expression's value but to the expression > >> itself, so it would not be any more efficient. (SQLite's optimizer > >> typically is not smart enough to detect and remove the duplication.) > >> > >> > >> As a workaround, you have to move the expression into a subquery in > >> the FROM clause: > >> > >> SELECT super_id, > >> (SELECT qux FROM baz WHERE id = super_id) > >> FROM (SELECT id as super_id FROM foo); > >> > >> > >> Regards, > >> Clemens > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users at mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
