Re: [sqlite] Error trying to do inner joins
Correct. For normal* cases inner joins are where you want only things that match in table A and table B, then you expand that out with multiple inner joins to get to 3 or more tables. Order doesn't matter in an inner join, so A INNER JOIN B will be equivalent to B INNER JOIN A. Outer joins are where you want everything that matches in A and B, but also want everything from table A that didn't match to B. Outer joins are order sensitive, so A LEFT OUTER JOIN B is different than B LEFT OUTER JOIN A * "Normal" being equi-joins of the type ...ON A.x = B.x (and A.y = B.y...) You can throw inequalities into the ON clause there, or just about any statement that can be cast to a boolean in the end, but 99% of the time you're gonna see just the = operator. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Lars Frederiksen Sent: Thursday, August 17, 2017 3:53 PM To: 'SQLite mailing list' Subject: Re: [sqlite] Error trying to do inner joins Thank you, This SQL works fine. Is the inner join normally used for connecting 3 tables where the "middle one" is a link table? /Lars -Oprindelig meddelelse- Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På vegne af David Raymond Sendt: 17. august 2017 21:16 Til: SQLite mailing list Emne: Re: [sqlite] Error trying to do inner joins There's an issue with the join statement in the first join you're joining Saetning and Verb_Saetn, but the ON clause uses fields in Verb_Saetn and Verbum, not Saetning. And then you join that back to Saetning, so you've joined Saetning twice and Verbum 0 times. Since the Saetning table shows up twice in your join that's why the fields are ambiguous. Try... ...FROM Saetning INNER JOIN Verb_Saetn ON Saetning.SAETNING_ID = Verb_Saetn.SAETNING_ID INNER JOIN Verbum ON Verb_Saetn.VERBUM_ID = Verbum.VERBUM_ID -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Lars Frederiksen Sent: Thursday, August 17, 2017 2:54 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Error trying to do inner joins Hello, I have a small db with 3 tables: CREATE TABLE IF NOT EXISTS "Verb_Saetn"( VS_ID INT PRIMARY KEY NOT NULL, VERBUM_ID INT NOT NULL, SAETNING_ID INT NOT NULL, FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID), FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID) ); CREATE TABLE IF NOT EXISTS "Verbum"( VERBUM_ID INT PRIMARY KEY NOT NULL, NUTID VARCHAR(40) NULL, DATID_IMPF VARCHAR(40) NULL, DATID_PERF VARCHAR(40) NULL, FREMTID_IMPF VARCHAR(40) NULL, FREMTID_PERF VARCHAR(40) NULL, GULINTNOT NULL, TYPE VARCHAR(20) NOT NULL, NOTE TEXTNULL ); CREATE TABLE IF NOT EXISTS "Saetning"( SAETNING_ID INT PRIMARY KEY NOT NULL, SENTENCE_EN VARCHAR(200) NOT NULL, SENTENCE_EL VARCHAR(200) NOT NULL ); The Verb_Saetn table is a link table between Verbum and Saetning (sentence in danish). I try this SQL text: SELECT SENTENCE_EN, SENTENCE_EL FROM Saetning INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID; But get this error: ambiguous column name: SENTENCE_EN - reason of this error? - is inner joins the correct way to "connect" two tebles via a "link-table"? Best Regards Lars ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error trying to do inner joins
Yes. Or you could toss the new fangled JOIN syntax (which does nothing if the join is not an outer join) which only adds silly complication and do something like: SELECT sentece_en, sentence_el FROM Saetning, Verb_Saetn, Verbum WHERE Saetning.Saeting_ID == Vert_Saetn.Saetning_ID AND Verb_Saetn.VERBUM_ID == Verbum.VERBUM_ID; Much more obvious what you are doing. Symantically it is exactly the same as David's query in the new fangled syntax since that query is merely written into the form above (the keyword JOIN or INNER JOIN is merely replaced by a ",", and the conditions in ON are merely pushed into the where clause and joined by AND). Since the contents of the ON clause are pushed down as where conditions, there is absolutely no requirement for the tables used in the ON clauses to have been seen prior to there use. They only need to exist when the optimizer attempts to generate a query plan (at least this is the case for all sane SQL processors, of which SQLite is one). You need to have indexes on your "foreign" key fields, and you probably want an index on Vert_Saetn containing the link keys Saetning_ID and VERBUM_ID, probably two indexes with the fields in both orders. Of course, that depends if you database will ever hold more than 10 records per table (if it will, then you will need all the indexes). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Lars Frederiksen >Sent: Thursday, 17 August, 2017 13:53 >To: 'SQLite mailing list' >Subject: Re: [sqlite] Error trying to do inner joins > >Thank you, > >This SQL works fine. >Is the inner join normally used for connecting 3 tables where the >"middle one" is a link table? > >/Lars > > > >-Oprindelig meddelelse- >Fra: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] På vegne af David Raymond >Sendt: 17. august 2017 21:16 >Til: SQLite mailing list >Emne: Re: [sqlite] Error trying to do inner joins > >There's an issue with the join statement in the first join you're >joining Saetning and Verb_Saetn, but the ON clause uses fields in >Verb_Saetn and Verbum, not Saetning. And then you join that back to >Saetning, so you've joined Saetning twice and Verbum 0 times. Since >the Saetning table shows up twice in your join that's why the fields >are ambiguous. > >Try... > >...FROM Saetning INNER JOIN Verb_Saetn ON Saetning.SAETNING_ID = >Verb_Saetn.SAETNING_ID INNER JOIN Verbum ON Verb_Saetn.VERBUM_ID = >Verbum.VERBUM_ID > >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Lars Frederiksen >Sent: Thursday, August 17, 2017 2:54 PM >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Error trying to do inner joins > >Hello, > > > >I have a small db with 3 tables: > > > > > >CREATE TABLE IF NOT EXISTS "Verb_Saetn"( > >VS_ID INT PRIMARY KEY NOT NULL, > >VERBUM_ID INT NOT NULL, > >SAETNING_ID INT NOT NULL, > >FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID), > >FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID) > >); > > > >CREATE TABLE IF NOT EXISTS "Verbum"( > > VERBUM_ID INT PRIMARY KEY NOT NULL, > > NUTID VARCHAR(40) NULL, > > DATID_IMPF VARCHAR(40) NULL, > > DATID_PERF VARCHAR(40) NULL, > > FREMTID_IMPF VARCHAR(40) NULL, > > FREMTID_PERF VARCHAR(40) NULL, > > GULINTNOT NULL, > > TYPE VARCHAR(20) NOT NULL, > > NOTE TEXTNULL > >); > > > >CREATE TABLE IF NOT EXISTS "Saetning"( > >SAETNING_ID INT PRIMARY KEY NOT NULL, > >SENTENCE_EN VARCHAR(200) NOT NULL, > >SENTENCE_EL VARCHAR(200) NOT NULL > >); > > > > > >The Verb_Saetn table is a link table between Verbum and Saetning >(sentence in danish). > >I try this SQL text: > > > > > >SELECT > >SENTENCE_EN, > >SENTENCE_EL > >FROM Saetning > >INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID > >INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID; > > > >But get this error: ambiguous column name: SENTENCE_EN > > > >- reason of this error? > >- is inner joins the correct way to "connect" two tebles via a "link- >table"? > > > >Best Regards > >Lars > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.
Re: [sqlite] Error trying to do inner joins
Thank you, This SQL works fine. Is the inner join normally used for connecting 3 tables where the "middle one" is a link table? /Lars -Oprindelig meddelelse- Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På vegne af David Raymond Sendt: 17. august 2017 21:16 Til: SQLite mailing list Emne: Re: [sqlite] Error trying to do inner joins There's an issue with the join statement in the first join you're joining Saetning and Verb_Saetn, but the ON clause uses fields in Verb_Saetn and Verbum, not Saetning. And then you join that back to Saetning, so you've joined Saetning twice and Verbum 0 times. Since the Saetning table shows up twice in your join that's why the fields are ambiguous. Try... ...FROM Saetning INNER JOIN Verb_Saetn ON Saetning.SAETNING_ID = Verb_Saetn.SAETNING_ID INNER JOIN Verbum ON Verb_Saetn.VERBUM_ID = Verbum.VERBUM_ID -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Lars Frederiksen Sent: Thursday, August 17, 2017 2:54 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Error trying to do inner joins Hello, I have a small db with 3 tables: CREATE TABLE IF NOT EXISTS "Verb_Saetn"( VS_ID INT PRIMARY KEY NOT NULL, VERBUM_ID INT NOT NULL, SAETNING_ID INT NOT NULL, FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID), FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID) ); CREATE TABLE IF NOT EXISTS "Verbum"( VERBUM_ID INT PRIMARY KEY NOT NULL, NUTID VARCHAR(40) NULL, DATID_IMPF VARCHAR(40) NULL, DATID_PERF VARCHAR(40) NULL, FREMTID_IMPF VARCHAR(40) NULL, FREMTID_PERF VARCHAR(40) NULL, GULINTNOT NULL, TYPE VARCHAR(20) NOT NULL, NOTE TEXTNULL ); CREATE TABLE IF NOT EXISTS "Saetning"( SAETNING_ID INT PRIMARY KEY NOT NULL, SENTENCE_EN VARCHAR(200) NOT NULL, SENTENCE_EL VARCHAR(200) NOT NULL ); The Verb_Saetn table is a link table between Verbum and Saetning (sentence in danish). I try this SQL text: SELECT SENTENCE_EN, SENTENCE_EL FROM Saetning INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID; But get this error: ambiguous column name: SENTENCE_EN - reason of this error? - is inner joins the correct way to "connect" two tebles via a "link-table"? Best Regards Lars ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error trying to do inner joins
There's an issue with the join statement in the first join you're joining Saetning and Verb_Saetn, but the ON clause uses fields in Verb_Saetn and Verbum, not Saetning. And then you join that back to Saetning, so you've joined Saetning twice and Verbum 0 times. Since the Saetning table shows up twice in your join that's why the fields are ambiguous. Try... ...FROM Saetning INNER JOIN Verb_Saetn ON Saetning.SAETNING_ID = Verb_Saetn.SAETNING_ID INNER JOIN Verbum ON Verb_Saetn.VERBUM_ID = Verbum.VERBUM_ID -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Lars Frederiksen Sent: Thursday, August 17, 2017 2:54 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Error trying to do inner joins Hello, I have a small db with 3 tables: CREATE TABLE IF NOT EXISTS "Verb_Saetn"( VS_ID INT PRIMARY KEY NOT NULL, VERBUM_ID INT NOT NULL, SAETNING_ID INT NOT NULL, FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID), FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID) ); CREATE TABLE IF NOT EXISTS "Verbum"( VERBUM_ID INT PRIMARY KEY NOT NULL, NUTID VARCHAR(40) NULL, DATID_IMPF VARCHAR(40) NULL, DATID_PERF VARCHAR(40) NULL, FREMTID_IMPF VARCHAR(40) NULL, FREMTID_PERF VARCHAR(40) NULL, GULINTNOT NULL, TYPE VARCHAR(20) NOT NULL, NOTE TEXTNULL ); CREATE TABLE IF NOT EXISTS "Saetning"( SAETNING_ID INT PRIMARY KEY NOT NULL, SENTENCE_EN VARCHAR(200) NOT NULL, SENTENCE_EL VARCHAR(200) NOT NULL ); The Verb_Saetn table is a link table between Verbum and Saetning (sentence in danish). I try this SQL text: SELECT SENTENCE_EN, SENTENCE_EL FROM Saetning INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID; But get this error: ambiguous column name: SENTENCE_EN - reason of this error? - is inner joins the correct way to "connect" two tebles via a "link-table"? Best Regards Lars ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error trying to do inner joins
On 2017/08/17 8:54 PM, Lars Frederiksen wrote: Hello, I have a small db with 3 tables: CREATE TABLE IF NOT EXISTS "Verb_Saetn"( VS_ID INT PRIMARY KEY NOT NULL, VERBUM_ID INT NOT NULL, SAETNING_ID INT NOT NULL, FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID), FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID) ); CREATE TABLE IF NOT EXISTS "Verbum"( VERBUM_ID INT PRIMARY KEY NOT NULL, NUTID VARCHAR(40) NULL, DATID_IMPF VARCHAR(40) NULL, DATID_PERF VARCHAR(40) NULL, FREMTID_IMPF VARCHAR(40) NULL, FREMTID_PERF VARCHAR(40) NULL, GULINTNOT NULL, TYPE VARCHAR(20) NOT NULL, NOTE TEXTNULL ); CREATE TABLE IF NOT EXISTS "Saetning"( SAETNING_ID INT PRIMARY KEY NOT NULL, SENTENCE_EN VARCHAR(200) NOT NULL, SENTENCE_EL VARCHAR(200) NOT NULL ); The Verb_Saetn table is a link table between Verbum and Saetning (sentence in danish). I try this SQL text: SELECT SENTENCE_EN, SENTENCE_EL FROM Saetning INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID; But get this error: ambiguous column name: SENTENCE_EN It means the Field SENTENCE_EN appears in more than one table and you did not qualify it, os the Query planner doesn't know which one you mean. Try: SELECT Saetning.SENTENCE_EN... - reason of this error? - is inner joins the correct way to "connect" two tebles via a "link-table"? Seems weird, it is better to put the filter references to a joined table AFTER the table is actually joined, but more importantly, where is table "Verbum" table being joined? You refer to it, but it's not in the join list. Did you perhaps mean to do this?: SELECT Saetning.SENTENCE_EN, Saetning.SENTENCE_EL FROM Saetning INNER JOIN Verb_Saetn ON Verb_Saetn.Saetning_ID = Saetning.Saetning_ID INNER JOIN Verbum ON Verbum.Verbum_ID = Verb_Saetn.Verbum_ID; Also - SQLite is very forgiving in terms of Case-sensitivity for Field, table and general object identifiers, but other engines will not know that Verbum_ID and VERBUM_ID is the same thing, and error out. Check your consistency. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error trying to do inner joins
Hello, I have a small db with 3 tables: CREATE TABLE IF NOT EXISTS "Verb_Saetn"( VS_ID INT PRIMARY KEY NOT NULL, VERBUM_ID INT NOT NULL, SAETNING_ID INT NOT NULL, FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID), FOREIGN KEY(SAETNING_ID) REFERENCES Saetning(SAETNING_ID) ); CREATE TABLE IF NOT EXISTS "Verbum"( VERBUM_ID INT PRIMARY KEY NOT NULL, NUTID VARCHAR(40) NULL, DATID_IMPF VARCHAR(40) NULL, DATID_PERF VARCHAR(40) NULL, FREMTID_IMPF VARCHAR(40) NULL, FREMTID_PERF VARCHAR(40) NULL, GULINTNOT NULL, TYPE VARCHAR(20) NOT NULL, NOTE TEXTNULL ); CREATE TABLE IF NOT EXISTS "Saetning"( SAETNING_ID INT PRIMARY KEY NOT NULL, SENTENCE_EN VARCHAR(200) NOT NULL, SENTENCE_EL VARCHAR(200) NOT NULL ); The Verb_Saetn table is a link table between Verbum and Saetning (sentence in danish). I try this SQL text: SELECT SENTENCE_EN, SENTENCE_EL FROM Saetning INNER JOIN Verb_Saetn ON Verb_Saetn.Verbum_ID = Verbum.Verbum_ID INNER JOIN Saetning ON Saetning.Saetning_ID = Verb_Saetn.Saetning_ID; But get this error: ambiguous column name: SENTENCE_EN - reason of this error? - is inner joins the correct way to "connect" two tebles via a "link-table"? Best Regards Lars ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
