select * from parent P, parent_child_1_link L, child_1 C where P.id = L.parent_id and C.id = L.child_id and P.child_type = 1 union select * from parent P, parent_child_2_link L, child_2 C where P.id = L.parent_id and C.id = L.child_id and P.child_type = 2
It is a very straightforward join. > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Paul > Sent: Monday, 8 February, 2016 04:57 > To: SQLite mailing list > Subject: Re: [sqlite] What's the reason for alias names not being visible > in subqueries? > > 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 > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
