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, GUL INTNOT NULL, TYPE VARCHAR(20) NOT NULL, NOTE TEXT NULL ); 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
